Help in Complex Difference query

Question

I need help to fix the following three issues in my query to get the desired result.

Note: The key columns are Name, code and PerID. My query is attached as a screenshot.

1- TableB’s key column BBBB-BB-003 is missing in TableA but result is not showing the keys columns, showing NULL instead of key columns.
2- I want the format of date1-yyyy-mm-dd
3- if Comments has null then it should replace with ‘ ‘ (blank)

Create TableA (Name varchar(20), Code varchar(20),PerID varchar(20), City varchar(20), Post varchar(20), Address varchar(20), Date1 datetime);
Insert into TableA values (‘Peter’, ‘AA’, ‘001’, ‘HH’, ‘1X’, ‘123 Cres’, ‘2022-01-01’)
Insert into TableA values (‘Smith’, ‘AA’, ‘002’, ‘HH’, ‘1X’, ‘123 Cres’, ‘2022-01-01’)
Insert into TableA values (‘John’, ‘DD’, ‘002’, ‘HH’, ‘1X’, ‘high place’, ‘2022-01-01’)
Insert into TableA values (‘Doren’, ‘EE’, ‘007’, ‘HH’, ‘1X’, ‘123 Cres’, ‘2022-01-01’)
Insert into TableA values (‘AAA’, ‘AA’, ‘003’, ‘HH’, ‘1X’, ‘67 Dr’, ‘2022-01-01’)

Create TableB (Name varchar(20), Code varchar(20),PerID varchar(20), City varchar(20), Post varchar(20), Address varchar(20), Date1 datetime);
Insert into TableB values (‘Peter’, ‘AA’, ‘001’, ‘HH’, ‘1X’, ‘777 Cres’, ‘2022-01-01’)
Insert into TableA values (‘Smith’, ‘AA’, ‘002’, ‘FRYN’, ‘1X’, ‘123 Cres’, ‘2022-01-01’)
Insert into TableA values (‘John’, ‘DD’, ‘002’, ‘HH’, ‘1X’, ‘high place’, ‘2022-01-01’)
Insert into TableA values (‘Doren’, ‘EE’, ‘007’, ‘HH’, ‘55X’, ‘123 Cres’, ‘2022-01-01’)
Insert into TableA values (‘BBB’, ‘BB’, ‘003’, ‘HH’, ‘1X’, ‘67 Dr’, ‘2022-01-01’)


I have added my query in three different parts which needs to be modified. 1st part of query was added in Apr 25, 2023, 8:28 AM, then Apr 25, 2023, 8:29 AM, and then Apr 25, 2023, 8:30 AM. Not sure why I was not able to post whole query at the same time.

SQL_Screenshot.jpg

Answer 1

Hi @Martin Kevin

How about this query?

;with CTE as(
  select case when A.Name is not null then A.Name else B.Name end as [Name],
         case when A.Code is not null then A.Code else B.Code end as [Code],
         case when A.PerID is not null then A.PerID else B.PerID end as [PerID],
         CASE WHEN A.City = B.City THEN '' ELSE isnull(A.City, B.City) END AS City,
         CASE WHEN A.Post = B.Post THEN '' ELSE isnull(A.Post, B.Post) END AS Post,
         CASE WHEN A.Address = B.Address THEN '' ELSE isnull(A.Address, B.Address) END AS Address,
         CASE WHEN A.Date1 = B.Date1 THEN '' ELSE 
		           isnull(convert(VARCHAR(20),A.Date1,23), convert(VARCHAR(20),B.Date1,23)) END AS Date1,
         case when A.Name is null then 'Missing in Table-A'
              when B.Name is null then 'Missing in Table-B' 
	          when A.Name = B.Name then '' end as Comments                     
  from TableA as A FULL outer join TableB as B on A.Name = B.Name)
Select * from CTE where city is not null 
  or post is not null or address is not null
  or date1 is not null or comments is not null;

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

When you check your table creation scripts again, you will find you have insert data row from tableB to tableA by mistake. Fix the error and rerun your query. I have a hard time to post the whole query as well. The site has too many problems.

Answer 3

Hi @Martin Kevin

I modified your query according to your needs, you can try it.

;with CTE as(
select case when A.Name is not null then A.Name else B.Name end as [Name],
case when A.Code is not null then A.Code else B.Code end as [Code],
case when A.PerID is not null then A.PerID else B.PerID end as [PerID],
CASE WHEN A.City = B.City THEN '' ELSE isnull(CAST(A.City AS VARCHAR(20)), '') END AS City,
CASE WHEN A.Post = B.Post THEN '' ELSE isnull(CAST(A.Post AS VARCHAR(20)), '') END AS Post,
CASE WHEN A.Address = B.Address THEN '' ELSE isnull(CAST(A.Address AS VARCHAR(20)), '') END AS Address,
CASE WHEN A.Date1 = B.Date1 THEN '' ELSE isnull(convert(VARCHAR(20),A.Date1,23), '') END AS Date1,
case when A.Name is null then 'Missing in Table-A'
    when B.Name is null then 'Missing in Table-B' 
	when A.Name = B.Name then '' end as Comments                     
  from TableA as A FULL outer join TableB as B on A.Name = B.Name)
Select * from CTE where city is not null 
or post is not null or address is not null
or date1 is not null or comments is not null;

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 4

Thanks. Row-6 is showing no data (City,Post,Address, Date1). All data should show because it is missing in table-A. Row-6 should shows data, as Row-5 is showing, as per it's data.