Hi @Mike ,
You could use full join instead of left join in your case after adding one row in #B.
Please refer the updated one from below and check whether it is helpful to you.
Select isnull(a.Icode,b.Icode) Icode,isnull(a.Qid,b.Qid) Qid, isnull(a.Qcode,b.Qcode) Qcode,isnull(a.Ifname,b.Ifname) Ifname, isnull(a.ILname,b.ILname) ILname, case when a.Qid=b.Qid and a.Qcode=b.Qcode then 'All records are same' when a.Qid<>b.Qid and a.Qcode=b.Qcode then 'Qid is not same' when a.Qid=b.Qid and a.Qcode<>b.Qcode then 'Qcode is not same' when b.Icode is null then 'This is missing in table #B' when a.Icode is null then 'This is missing in table #A' end Comments from #A a full join #B b on a.Icode=b.Icode and a.Ifname=b.Ifname and a.ILname=b.ILname order by isnull(a.Icode,b.Icode),isnull(a.Qid,b.Qid)
Icode Qid Qcode Ifname ILname Comments 1929 10 A Smith Sam All records are same 1929 11 B Jane Alam All records are same 1930 11 B Filip John All records are same 1930 12 A Sam John Qid is not same 1930 13 C Johni John Qid is not same 1931 11 A Jimmi Khan All records are same 1931 12 B Marci Khan This is missing in table #B 1939 12 A John K This is missing in table #A
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table