SELECT items WHERE ID is not in another selection

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)

SELECT items WHERE ID is not in another selection

Author

Topic

adjones1980
Starting Member

36 Posts

Posted - 2007-07-06 : 11:41:31

I need to do a select statement where I select items where their IDs are not in another table.

Psuedo code would be something like...

SELECT ItemID, ItemName
FROM Table1
WHERE ItemID <> (SELECT DISTINCT ItemID FROM Table2 ORDER BY ItemID)

Any ideas

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-06 : 11:43:53

[code]SELECT ItemID, ItemName
FROM Table1 t1
WHERE not exists (SELECT * FROM Table2 t2 where t2.ItemID = t1.ItemID)

SELECT t1.ItemID, t1.ItemName
FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.ItemID = t2.ItemID
Where t2.ItemID IS NULL
[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2007-07-06 : 12:04:41

SELECT ItemID, ItemName
FROM Table1
WHERE ItemID not in (SELECT DISTINCT ItemID FROM Table2 ORDER BY ItemID)

---
"Try not to become a man of success but rather try to become a man of value."

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-06 : 12:24:23

NOT IN is not good candidate performance wise. Plus it has known issues with handling of NULLs.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

adjones1980
Starting Member

36 Posts

Posted - 2007-07-06 : 12:50:46

Thx for the help guys! Plenty to choose from!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources