Select Records that don't exist 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

Select Records that don't exist in another table

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-01-17 : 10:13:35

I'm working with two tables.

IMINVLOC
IMINVBIN

I want a select statement that will show me what records exist in the IMINVLOC that are not present in the IMINVBIN.

These two tables will link on item_no and loc

select iminvloc_sql.item_no
from ???

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-17 : 10:19:14

SELECT LOC.*
FROM IMINVLOC LOC
LEFT JOIN IMINVBIN BIN
ON loc.item_no = bin.item_no
and loc.loc = bin.loc
WHERE bin.loc is null or bin.loc is null

SELECT loc.*
FROM IMINVLOC LOC
WHERE NOT EXISTS
(select 1
from IMINVBIN BIN
where loc.item_no = bin.item_no
and loc.loc = bin.loc
)

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-18 : 09:00:33

From versions 2005 onwards you can use EXCEPT operator too

select * from IMINVLOC
except
select * from IMINVBIN

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources