select query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

select query

Author

Topic

noms
Starting Member

22 Posts

Posted - 2009-02-19 : 12:41:05

hi expert
i have two tables: i need to select all records from tableA which are not in tableB and vice versa, i know i can do it with using two separate selects statements "Select * from TableA where ID not in (Select ID from TableB)" vice versa but that will give me two separate records,
i need to display results using one query

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 13:29:29

[code]Select t.* from TableA t
Where not exists(Select * from TableB where ID = t.ID)[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 13:31:11

[code]Select t.* from TableA t
left outer join TableB m
on t.ID = m.ID
Where m.ID is null[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 13:32:01

For distinct:

Select * from TableA
Except
Select * from TableB

noms
Starting Member

22 Posts

Posted - 2009-02-19 : 13:41:27

thanks for the response, but this query will give me records from one table whereas i need records from both the tables where tableA not in tableB and tableB not in TableA in one query result

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 13:43:40

Can't you switch it? Hello....

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-19 : 14:07:55

Noms..I dont get the logic of doing this...assume..you do get the IDs that are there in A and not in B and vice versa in a single query...

with the IDs that you get , how will you know which ID is missing in which table?

Can you maybe explain why two SELECTS wont work for you?

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-19 : 14:19:42

vijayisonly has a very good point.

This should get you 2 columns, 1 column of IDs from TableA that are missing from TableB, and 1 column of IDs from TableB that are missing from TableA.

select a.id as tbla_id,
b.id as tblb_id
from TableA a
full outer join TabeB b on a.id = b.id
where a.id is null or b.id is null

Hope this helps.

noms
Starting Member

22 Posts

Posted - 2009-02-20 : 01:34:33

i think you have a good point there vijayisonly as well, i used the latter query the full outer join and got the results i need,
thanks and its worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources