how can we get the data ith out not in

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

how can we get the data ith out not in

Author

Topic

boreddy
Posting Yak Master

172 Posts

Posted - 2009-04-10 : 02:33:16

Hi experts

i have five tables in my databse it has same column 'ID'.
i need to get the data from frist table these IDs doesn't in the remaining four tables,here we cant use the NOT IN

thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-10 : 03:49:03

SELECT t1.ID
FROM Table1 AS t1
LEFT JOIN (SELECT ID FROM Table2 UNION ALL SELECT ID FROM Table3 UNION ALL SELECT ID FROM Table4 UNION ALL SELECT ID FROM Table5) AS x ON x.ID = t1.ID
WHERE x.ID IS NULL

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-10 : 03:50:05

SELECT t1.ID
FROM Table1 AS t1
WHERE NOT EXISTS (SELECT * FROM Table2 AS t2 WHERE t2.ID = t1.ID)
AND NOT EXISTS (SELECT * FROM Table3 AS t3 WHERE t3.ID = t1.ID)
AND NOT EXISTS (SELECT * FROM Table4 AS t4 WHERE t4.ID = t1.ID)
AND NOT EXISTS (SELECT * FROM Table5 AS t5 WHERE t5.ID = t1.ID)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources