Selecting all values that are NOT in another table

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

General SQL Server Forums

New to SQL Server Programming

Selecting all values that are NOT in another table

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-10-31 : 18:03:20

I need to select all values that are currently not in another table. This is so confusing to me that I can't even really think of how to explain it. Here is an example...

select * 
from		Store
			join Organization
				on store.organizationID = Organization.organizationID
			left join StoreExport
				on store.storeID = StoreExport.storeID

where		Organization.organizationID = 1 and
			StoreExport.storeID <> Store.storeID

Example data:


  Store                      StoreExport
storeID   1                   storeID   3
storeID   2                   storeID   4
storeID   3                   storeID   5
storeID   4                   
storeID   5

I'm looking for my query to only return records who have a storeID of 1 and 2 since 3,4, and 5 are all in StoreExport.

Does this make sense? Sorry it's so confusing...

crugerenator
Posting Yak Master

126 Posts

Posted - 2008-10-31 : 18:11:50

Figured it out!


select * 
from		Store
			join Organization
				on store.organizationID = Organization.organizationID
			left join StoreExport
				on store.storeID = StoreExport.storeID

where		Organization.organizationID = 1 and
			storeExport.storeID is null

It just clicked out of nowhere. Thanks for the help if you're typing a response right now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources