problem with select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

problem with select

Author

Topic

casati74
Posting Yak Master

109 Posts

Posted - 2006-08-24 : 07:57:33

Hello,
i have a table with any value flag by yes; in the same table i can have the same value with a flag no, in this case i don't select it.
How can i select only value with flag yes that they do not have also a flag not????

es:

id desc falg
1 pippo yes
1 pippo not
2 pluto yes

I must select only id = 2

How can i do it??????????
Thank's

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-24 : 08:00:44

Select * from TableName Where Flag = 'Yes' ??

you need to learn sql check out the following links..

http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.as

Chirag

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-08-24 : 08:11:01

I'm thinking you wanted something like this...

--data
declare @t table (id int, [desc] varchar(10), flag varchar(10))
insert @t
          select 1, 'pippo', 'yes'
union all select 1, 'pippo', 'not'
union all select 2, 'pluto', 'yes'
union all select 3, 'pluto', 'yes'
union all select 3, 'pippo', 'yes'

--calculation
select * from @t where flag = 'yes' and id not in (select id from @t where flag = 'not')

--or
select * from @t a where flag = 'yes' and not exists (select id from @t where flag = 'not' and id = a.id)

--results
/*
id          desc       flag       
----------- ---------- ---------- 
2           pluto      yes
3           pluto      yes
3           pippo      yes
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-24 : 08:14:15

[code]create table #t([id] int, [desc] varchar(100), flag varchar(5))

insert into #t values (1,'pippo', 'Yes')
insert into #t values (1,'pippo', 'No')
insert into #t values (2,'pippo', 'Yes')

Select * from #t where
flag = 'Yes' and [id] not in (select [ID] from #t where flag = 'No')

drop table #t[/code]

uuuuuuuuuh

Srinika

casati74
Posting Yak Master

109 Posts

Posted - 2006-08-24 : 08:48:37

ok!!!!

but my problem is when i have a lot of 2, 'pluto', 'yes'

how can i select the values with the earliest data date???

Es:

2, 'pluto', 'yes' 06/08/13 15.00
2, 'pluto', 'yes' 06/08/13 15.01
2, 'pluto', 'yes' 06/08/13 15.02
2, 'pluto', 'yes' 06/08/13 15.03

I must select 2, 'pluto', 'yes' 06/08/13 15.03

How can i do it????

Thank's a lot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 08:50:19

Where did the flag column go?

Peter Larsson
Helsingborg, Sweden

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-24 : 08:54:57

U can use the MIN function along with Group By

Ur date data is incorrect!! Is it of datetime or varchar data type ?

Srinika

casati74
Posting Yak Master

109 Posts

Posted - 2006-08-24 : 09:19:49

now i have:

select IdReplication, InvolvedTable, IdRegister, OperationType, RegisterDate from Replications
where (OperationType='I' or OperationType='U') and IdRegister not in (select IdRegister from Replications where OperationType= 'D')
order by IdRegister

This select return a lot of row with the same IdRegister, InvolvedTable and OperationType in the same data (the colum is date)

I must select only one of this row

i wrote and (MAX(RegisterDate) from Replication) but he must be the max RegisterDate of the same IdRegister and the Same InvolvedTable, and the same OperationType.

es:
2, 'pluto', 'yes' 06/08/13 15.00
2, 'pluto', 'yes' 06/08/13 15.01
2, 'pluto', 'yes' 06/08/13 15.02
2, 'pluto', 'yes' 06/08/13 15.03

I must select 2, 'pluto', 'yes' 06/08/13 15.03

Thank's a lot for your help, for me it's very difficult

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-24 : 14:19:17

Try following the first link in my signature, and restating your question.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources