How to do this query? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

How to do this query?

Author

Topic

donar
Starting Member

22 Posts

Posted - 2005-06-15 : 12:39:48

Hi, guys
I have a table with 4 fields: ISN, type, sequence, property. The ISN, type, sequence together is the primary key. Now I am trying to find out ISNs with no purticular type.

create table #temp
(isn int,
type varchar(20),
sequence int,
property varchar(65))

insert into #temp
select 9,'AGE',1,'Ordovician' union all
select 9,'AGE',2,'Cambrian' union all
select 9,'AREA',1,'Nova Scotia, southern' union all
select 9,'KEYWORD',1,'veins, stratabound' union all
select 97,'KEYWORD',1,'veins' union all
select 108,'KEYWORD',2,'veins, fissure'

I would like to find out which ISNs does not have type='AGE', how can I do it?

The sequenc in the table does not have real meaning, just use it to make the first three fields as a key. sequence could be not sequencial.

Thanks!

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-06-15 : 13:18:11

Is this what you want?

SELECT DISTINCT isn
FROM #temp
WHERE type <> 'AGE'

KLang23
Posting Yak Master

115 Posts

Posted - 2005-06-15 : 13:43:58

I think this should do it:


Select Distinct isn from #Temp T1 Where type <> 'AGE'
   and Not Exists (Select * From #Temp T2
                    Where T1.isn = T2.isn
                      and T2.type = 'AGE')

This will return all isn's that have a row that isn't type "AGE" that don't have a row that is type "AGE"

Returns:

isn         
----------- 
97
108
[/code]

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources