Checking values in tables

Question

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

Answer 1

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 table

Answer 2

> I have to compare tabl3 Alphas and Betas. Icode is the key and Icode could be many child records [sic]. Just want to make sure both tables has same records [sic] of each Icode. If any column has mismatch values or missing record(s)[sic] in any table then I want in comments to know. <<

You're missing a lot of basic concepts you actually have to declare a key in a table. This is, by definition, not an option. Rows are nothing whatsoever like records! You also don't seem to know that you can insert a table constructor in a single statement and get some optimization. We also hate using the proprietary # tables. It is essential you're still thinking in terms of records and magnetic tapes. Let's try and fix what you've got and turn it into proper SQL.

CREATE TABLE Alphas
(Icode CHAR(10) NOT NULL,
q_id INTEGER NOT NULL,
PRIMARY KEY (Icode, q_id), --- my best guess!
q_code CHAR(2) NOT NULL
CHECK (q_code IN ('A', 'B', 'C')),
lf_name CHAR(10) NOT NULL,
Il_name CHAR(10) NOT NULL);

INSERT INTO Alphas
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');

CREATE TABLE Betas
(Icode CHAR(10) NOT NULL, ,
q_id INTEGER NOT NULL,
PRIMARY KEY (Icode, q_id),
q_code CHAR(2) NOT NULL
CHECK (q_code IN ('A', 'B', 'C')),
lf_name CHAR(10) NOT NULL,
Il_name CHAR(10) NOT NULL);

INSERT INTO Betas
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');

I hope you've read some Chris Date books, so you won't think I'm the only one that harps on this. Your design is completely wrong. No schema should have tables with identical structures. You have committed a sin that I've written about in the literature called "attributes splitting" and it's easy to explain. If you have a table called "male_personnel" and a table called "female_personnel", you have split, what should have been a table called "personnel" on the attribute "sex_code" and you just made all of your queries insanely complicated.

A lot of times these splits are done on sources or temporal values because that's the way magnetic tapes and their records (records are not rows) would be merged in the 1960s tape filesystems.

We have set theory operators in SQL and have had them for several years. For example, set difference can be expressed as:

SELECT X. *
FROM (SELECT * FROM Alphas
EXCEPT ALL
SELECT * FROM Betas) AS X;

if the two tables are identical, this will result in an empty set. But it gives you actually is the things that appear an alpha only. You can do all sorts of combinations of INTERSECT ALL, UNION ALL and EXCEPT ALL , along with explicit SELECT list.