CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
Prady
Prady

Reputation: 11310

Sql Query help to get non matching records from two tables

I am trying to get non matching records from 2 tables

For ex

TableA
 ID           Account
 1               Acc1
 2               Acc2
 3               Acc3

 TableB
 Opp          Accountid
 Opp1            1
 Opp2            2
 Opp3            4

I need to know which accountid is present in TableB but not in TableA. It would be wonderful if someone could provide this query.

Required record would be Opp3 of tableB

Thanks

Prady

Upvotes: 16

Views: 141604

Answers (5)

sajid
sajid

Reputation: 113

This will generate same results.

select * from TableB where Accountid not in (select ID from TableA)

Upvotes: 3

David Fells
David Fells

Reputation: 6798

SELECT B.Accountid 
  FROM TableB AS B 
  LEFT 
  JOIN TableA AS A 
    ON A.ID = B.Accountid 
 WHERE A.ID IS NULL;

LEFT JOIN means it takes all the rows from the first table - if there are no matches on the first join condition, the result table columns for table B will be null - that's why it works.

Upvotes: 22

Kumaran NJ
Kumaran NJ

Reputation: 59

SELECT B.Accountid
FROM TableB AS B 
LEFT JOIN TableA AS A ON A.ID = B.Accountid 
WHERE A.ID IS NULL

Upvotes: 5

Kuntady Nithesh
Kuntady Nithesh

Reputation: 11721

try this 

(select * from t1
except 
select * from t2)

union

(select * from t2
except 
select * from t1)

thinking that you have the same number of columns in both tables

query mentioned above select ids from #two EXCEPT select id from #one will give u non matching rows from only #two . it will neglect that of #one

Upvotes: 3

Nighil
Nighil

Reputation: 4129

create table #one (id int,acc nvarchar(25))
insert into #one (id , acc) values(1,'one') 
insert into #one (id , acc) values(2,'two') 
insert into #one (id , acc) values(3,'three') 

create table #two (acct nvarchar(25),ids int)
insert into #two (acct,ids) values('one',1) 
insert into #two (acct,ids) values('two',3) 
insert into #two (acct,ids) values('four',4) 

select ids from #two EXCEPT select id from #one 

drop table #one 
drop table #two

test this one

Upvotes: 13

Related Questions