Compare the data from the two tables

Question

Hello.

I have two product sheets that record some information about the product.

---

Table1

ID Long Wide High Date

AA 25 21 3 3/6/2022

BB 15 37 9 4/8/2022

CC 57 21 4 9/27/2022

DD 32 8 6 1/16/2023

---

Table2

ID Long Wide High Date

AA 23 21 3 3/6/2022

BB 15 37 8 4/8/2022

CC 57 21 4 9/28/2022

DD 32 8 6 1/16/2023

Now I need to compare two tables. ID column is unique.

Expected output:

---

Thanks in advance.

Answer 1

Hi @vava-8173

You can try this query.

create table table1(ID char(2),Long int,Wide int,High int,Date datetime)
insert into table1 values
('AA',25,21,3,'2022-03-06'),
('BB',15,37,9,'2022-04-08'),
('CC',57,21,4,'2022-09-27'),
('DD',32,8,6,'2023-01-16');

create table table2(ID char(2),Long int,Wide int,High int,Date datetime)
insert into table2 values
('AA',23,21,3,'2022-03-06'),
('BB',15,37,8,'2022-04-08'),
('CC',57,21,4,'2022-09-28'),
('DD',32,8,6,'2023-01-16');


select A.ID,case when A.Long = B.Long then '' else 'discrepancy' end as Long,
       case when A.Wide = B.Wide then '' else 'discrepancy' end as Wide,
	   case when A.High = B.High then '' else 'discrepancy' end as High,
	   case when A.Date = B.Date then '' else 'discrepancy' end as Date
from table1 as A inner join table2 as B on A.ID = B.ID;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

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.

Answer 2

Like this? I hop you get the rest on your own.

SELECT T1.Id
       CASE WHEN T1.Long <> T2.Long THEN 'discrepancy' ELSE '' END AS Long
FROM Table1 AS T1
     INNER JOIN
     Table2 AS t2
         ON T1.ID = T2.Id