-
MelissaMa-MSFT 24,131 Reputation points
2020-12-01T01:07:53.333+00:00 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)
Output:
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
Best regards
Melissa
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 table5 additional answers
Sort by: Most helpful
-
Sharon Zhao-MSFT 24,706 Reputation points Microsoft Vendor
2020-11-30T08:23:47.557+00:00 @Mike ,
As we are mainly responsible for general issue of Microsoft Teams. Your question which is involved in SQL Server is not included in Teams, I would remove the office-teams-windows-itpro tag and add the sql-server-transact-sql tag. Hope you get better response.
If the response 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.
MelissaMa-MSFT 24,131 Reputation points2020-11-30T08:40:10.813+00:00 Hi @Mike ,
Please refer below:
select A.*, 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' end Comments from #A a left join #B b on a.Icode=b.Icode and a.Ifname=b.Ifname and a.ILname=b.ILname order by a.Icode,a.Qid
Output:
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
Best regards
Melissa
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 tableMike 301 Reputation points2020-11-30T17:59:52.487+00:00 Hi,
I have added the following row in #B but not in #A but I don't see the comments i.e. 'This is missing in table #A' from query.Insert into #B values ('1939',12,'A','John','K')
Select A.*,
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
left join #B b
on a.Icode=b.Icode and a.Ifname=b.Ifname and a.ILname=b.ILname
order by a.Icode,a.QidYitzhak Khabinsky 20,646 Reputation points2020-11-30T18:16:51.587+00:00 It is better to use set based operators
EXCEPT
andINTERSECT
for such comparisons.
Check it out below.SQL
-- DDL and sample data population, start DECLARE @tbl_a table (Icode char(10), Qid int, Qcode char(2), Ifname char(10), ILname char(10)) Insert into @tbl_a values ('1929',10,'A','Smith','Sam'), ('1929',11,'B','Jane','Alam'), ('1930',11,'B','Filip','John'), ('1930',12,'A','Sam','John'), ('1930',13,'C','Johni','John'), ('1931',11,'A','Jimmi','Khan'), ('1931',12,'B','Marci','Khan'); DECLARE @tbl_b TABLE (Icode char(10), Qid int, Qcode char(2), Ifname char(10), ILname char(10)) Insert into @tbl_b values ('1929',10,'A','Smith','Sam'), ('1929',11,'B','Jane','Alam'), ('1930',11,'B','Filip','John'), ('1930',13,'A','Sam','John'), ('1930',12,'C','Johni','John'), ('1931',11,'A','Jimmi','Khan'); -- DDL and sample data population, end SELECT *, 'tbl_a different row' FROM @tbl_a EXCEPT SELECT *, 'tbl_a different row' FROM @tbl_b; SELECT *, 'identical rows' FROM @tbl_a INTERSECT SELECT *, 'identical rows' FROM @tbl_b; SELECT *, 'tbl_b different row' FROM @tbl_b EXCEPT SELECT *, 'tbl_b different row' FROM @tbl_a;
-
Checking values in tables

Hello, I have to compare tabl3 #A and #B. Icode is the key and Icode could be many child records. Just want to make sure both tables has same records of each Icode. If any column has mismatch values or missing record(s) in any table then I want in comments to know.
Drop table #A,#B
create table #A (Icode char(10), Qid int, Qcode char(2), Ifname char(10), ILname char(10))
Insert into #A values ('1929',10,'A','Smith','Sam')
Insert into #A values ('1929',11,'B','Jane','Alam')
Insert into #A values ('1930',11,'B','Filip','John')
Insert into #A values ('1930',12,'A','Sam','John')
Insert into #A values ('1930',13,'C','Johni','John')
Insert into #A values ('1931',11,'A','Jimmi','Khan')
Insert into #A values ('1931',12,'B','Marci','Khan')
create table #B (Icode char(10), Qid int, Qcode char(2), Ifname char(10), ILname char(10))
Insert into #B values ('1929',10,'A','Smith','Sam')
Insert into #B values ('1929',11,'B','Jane','Alam')
Insert into #B values ('1930',11,'B','Filip','John')
Insert into #B values ('1930',13,'A','Sam','John')
Insert into #B values ('1930',12,'C','Johni','John')
Insert into #B values ('1931',11,'A','Jimmi','Khan')
/Expected Result/
Select * from #A
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 Qid is not same
1930 12 A Sam John Qid is not same
1930 13 C Johni John Qid is not same
1931 11 A Jimmi Khan
1931 12 B Marci Khan This is missing in table #B