Sql Query helps get inappropriate records from two tables

Question

I am trying to get inappropriate entries 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 account is present in TableB but not available in TableA. It would be great if someone could explain how you approach this query.

The required entry will be Opp3 table B

thanks

Prady

Answer 1

 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 

check this

Answer 2

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

LEFT JOIN means that it takes all the rows from the first table - if there are no matches in the first join condition, the columns of the result table for table B will be zero - then why they work.

Answer 3

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

Answer 4

try it

 (select * from t1 except select * from t2) union (select * from t2 except select * from t1) 

thinking you have the same number of columns in both tables

mentioned above, select identifiers from #two EXCEPT, select id from #one will give u inappropriate lines from #two. he will neglect that of #one