join tables - SQL Server Forums

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

join tables

Author

Topic

vasu4us
Posting Yak Master

102 Posts

Posted - 2006-08-30 : 08:45:14

i have 2 tables with a common ID in both tables.
one table has more number of rows than the other
i want to a query which gives the list of ids or rows which are missing in the other table.

left,right,inner jopins only give me common or common + right/left
i want only the missing once.

can someone help me with this

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 08:50:28

TableA has the "more number" records.

SELECT TableA.*
FROM TableA
LEFT JOIN TableB ON TableB.ID = TableA.ID
WHERE TableB.ID IS NULL

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-30 : 09:41:34

or this....

SELECT * FROM TABLEA X WHERE NOT EXISTS (SELECT * FROM TABLEB Y WHERE Y.ID = X.ID)

Harsh Athalye
India.
"Nothing is Impossible"

vasu4us
Posting Yak Master

102 Posts

Posted - 2006-08-30 : 11:03:39

thanks
two solutions worked but when i count the 2 tables the difference is 10006 rows
but when i run this query iam geting 12310 rows. iam not able to justify why this difference

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 11:40:15

You most probably have duplicate IDs somewhere.

SELECT COUNT(DISTINCT ID), COUNT(ID) FROM TableA
SELECT COUNT(DISTINCT ID), COUNT(ID) FROM TableB

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources