SQL- Conditional Query Correction Help

Question

Hi, Please review the SQL query and help.

I need help to fix the following two issues in my query to get the desired result. Note: The key columns are Name, code and PerID which can not be same.

1- Example: 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. Should show key columns with data and with comments that 'Missing in Table-A' and same if TableA's key column is missing in TableB then it should all the data and with comments that 'Missing in Table-B'

2- if Comments has null then it should replace with ‘ ‘ (blank).

Note:I want to keep VARCHAR(20) to convert in varchar with every column in select query. Currently I am using VARCHAR(20) to convert which want to keep.

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’, ‘123 Cres’, ‘2022-01-01’)

Insert into TableB values (‘Smith’, ‘AA’, ‘002’, ‘FRYN’, ‘1X’, ‘123 Cres’, ‘2022-01-01’)

Insert into TableB values (‘John’, ‘DD’, ‘002’, ‘HH’, ‘1X’, ‘high place’, ‘2022-01-01’)

Insert into TableB values (‘Doren’, ‘EE’, ‘007’, ‘HH’, ‘55X’, ‘123 Cres’, ‘2022-01-01’)

Insert into TableB values (‘BBB’, ‘BB’, ‘003’, ‘HH’, ‘1X’, ‘67 Dr’, ‘2022-01-01’)

with CTE as(

select A.Name,A.Code,A.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' end as Comments /Remaining 3 lines of query in next answer's block*/

Answer 1

Hi @Kenny Gua

I encountered almost the same problem as yours half a month ago.

I don't know exactly why, but you can look at the solution to that problem.

https://learn.microsoft.com/en-us/answers/questions/1254662/help-in-complex-difference-query

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

First permit me some comments on the post as such.

It's great that you provide CREATE TABLE + INSERT. However, we really appreciate if you test your script before you post it, so that it compiles, run, and actually demonstrates the issue you are having. I had to correct some syntax errors (TABLE missing after CREATE and replacing "smart" quotes with proper single quotes). It also seemed that in the second set of INSERT, you meant to insert all rows into TableB, although the script actually said TableA.

Also, if you make sure that there is some indentation when you paste the code, the editor will automatically apply code formatting, which makes it easier to read. (But this applies to the code only. Don't paste your question with the code.)

So over to the questions. For the first question, you need to use the isnull function. The isnull function takes two arguments. If the first argument is non-NULL, that argument is return, else the second argument returned. In this case, this part of the CTE:

select A.Name,A.Code,A.PerID,

needs to be written as:

   select isnull(A.Name, B.Name) AS Name, isnull(A.Code, B.Code) AS Code, isnull(A.PerID, B.PerID) AS PerID,

So that you can get the key value from any of the two tables. (And then you need to introduce aliases, so that the result columns have a name.)

For the comments column, it's simple. In a CASE expression, you can add an ELSE clause which specifies the value when none of the WHEN conditions are true:

          case when A.Name is null then 'Missing in Table-A'
                when B.Name is null then 'Missing in Table-B' 
                else ''
          end as Comments