Query results that are not in another query

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 2005 Forums

Transact-SQL (2005)

Query results that are not in another query

Author

Topic

mihamil
Starting Member

8 Posts

Posted - 2008-08-15 : 13:42:44

I have this query which returns all active offices in our database.

select Office_Number, Association_ID, Status
from Office_Association
where Association_ID in ('STL','STC','ECBR','FRN','JEF')
and Status='A'

I want to get a list of all offices in the same table that are not included in the results from the query above.

I cannot think of a way to do this? Could you please help?

Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-15 : 13:47:33

The opposite of your query would be NOT IN.

You could also do this with NOT EXISTS.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 13:55:58

[code]SELECT oa.Office_Number, oa.Association_ID, oa.Status
FROM Office_Association oa
LEFT JOIN (select Association_ID
from Office_Association
where Association_ID in ('STL','STC','ECBR','FRN','JEF')
and Status='A')tmp
ON tmp.Association_ID=oa.Association_ID
WHERE tmp.Association_ID IS NULL[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources