How to show Missing Data Using LeftOuterJoin or Any Join Methods

Question

How to show Missing Data Using LeftOuterJoin or Any Join Methods

Carlton Patterson 41

Hello Forum

I have an original table and data as follows:
CREATE TABLE #tmpTable (
id int,
FirstName varchar(50),
LastName varchar(50),
Email varchar(50),
Department varchar(50),
Salary int)

INSERT #tmpTable VALUES  
(6,'RameshTheThird','Babu','******@pramati.co','Politics',15000),  
(2,'Daniel','meng','******@stanford.edu','Delivery',20000),  
(8,'Sampath','Kumar','******@pramati.co','Advertising',16000),  
(7,'Bipul','Kumar','******@pramati.co','Research',14000),  
(3,'Muriel',NULL,'******@waterloo.edu','Marketing',13000),  
(1,'Basher','armbrust','******@gmail.com','HR',10000),  
(4,'Rachel','wendell','******@imaginea.com','Engineering',70000),  
(5,'Zach','galifianakis','******@pramati.co','Analyst',87777),  
(9,'Anil','Reddy','******@pramati.co','Business',78000),  
(10,'Mageswaran','Dhandapani','******@pramati.co','Law',88000)

I then an updated table as follows:

CREATE TABLE #tmpTable (  
    id int,  
    FirstName varchar(50),  
    LastName varchar(50),  
    Email varchar(50),  
    Department varchar(50),  
    Salary int)  
  
INSERT #tmpTable VALUES  
(13,'Carlton','Patterson','******@keyloop.co.uk','Data Engineering',80000),  
(10,'Mageswaran','Dhandapani','******@pramati.co','Law',88000),  
(3,'Muriel','Somebody','******@waterloo.edu','BrickLayer',66000),  
(6,'RameshTheThird','Babu','******@pramati.co','Politics',15000),  
(4,'Rachel','wendell','******@imaginea.com','Engineering',70000),  
(2,'Daniel','Peterson','******@stanford.edu','Scientist',20000),  
(5,'Zach','galifianakis','******@pramati.co','Analyst',87777),  
(8,'Sampath','Kumar','******@pramati.co','Advertising',16000),  
(7,'Bipul','Kumar','******@pramati.co','Research',14000),  
(9,'Anil','Reddy','******@pramati.co','Business',78000),  
(1,'Basher','armbrust','******@gmail.com','HR',666)

As you can see from the updated table there has been some changes to Department, Salaries and LastName values

I have compiled the following code with varioius leftouterjoin to capture the updates in the second updated table

SELECT  
  ORIGINALv1.FirstName  
 ,ORIGINALv1.LastName  
 ,ORIGINALv1.Department  
 ,ORIGINALv1.Salary  
 ,UPDATED.Salary  
 ,UPDATED.Department  
 ,UPDATED.LastName  
FROM dbo.ORIGINALv1  
LEFT OUTER JOIN dbo.UPDATED  
  ON ORIGINALv1.Salary = UPDATED.Salary  
    AND ORIGINALv1.Department = UPDATED.Department  
    AND ORIGINALv1.LastName = UPDATED.LastName

The table names have changed in my code, but the data is the same.

My code doesn't actually show the changes.

Can someone show me how to modify my query to show the changes?

Accepted answer

1 additional answer

Your answer

Answer 1

Guoxiong 8,206

Try this:

-- Updated rows
SELECT 
  UPDATED.id
 ,UPDATED.FirstName
 ,UPDATED.LastName
 ,UPDATED.Department
 ,UPDATED.Salary
FROM dbo.UPDATED
INNER JOIN dbo.ORIGINALv1 ON UPDATED.id = ORIGINALv1.id AND (
 UPDATED.FirstName <> ORIGINALv1.FirstName OR 
 UPDATED.Department <> ORIGINALv1.Department OR 
 UPDATED.Salary <> ORIGINALv1.Salary
)
UNION
-- New rows
SELECT 
  UPDATED.id
 ,UPDATED.FirstName
 ,UPDATED.LastName
 ,UPDATED.Department
 ,UPDATED.Salary
FROM dbo.UPDATED
WHERE UPDATED.id NOT IN (SELECT id FROM ORIGINALv1)
ORDER BY id;

Carlton Patterson 41 Reputation points

2020-09-05T18:36:42.153+00:00

Hi @Guoxiong

Thanks for reaching out.

I'm getting the following errors with your code:

1 Invalid column name 'UPDATED'.

2 Ambiguous column name 'id'.

3 Invalid column name 'UPDATED'.

Any thoughts?
Carlton Patterson 41 Reputation points

2020-09-05T18:39:50.627+00:00

@Guoxiong , I figured it out. There is a comma, instead of the period.

Otherwise, this is perfect

Thanks
Carlton Patterson 41 Reputation points

2020-09-05T18:42:21.563+00:00

@Guoxiong , I just have one question.

Is it possible to achieve the same result without doing a UNION?
Guoxiong 8,206 Reputation points

2020-09-05T18:42:41.73+00:00

Sorry I mistyped it. I corrected it in the code. Thanks.

Guoxiong 8,206

Here is the script without UNION:

SELECT 
     UPDATED.id
    ,UPDATED.FirstName
    ,UPDATED.LastName
    ,UPDATED.Department
    ,UPDATED.Salary
FROM dbo.UPDATED
WHERE UPDATED.id NOT IN (SELECT id FROM ORIGINALv1) 
OR EXISTS (
    SELECT 1 
    FROM dbo.ORIGINALv1 
    WHERE id = UPDATED.id AND (
        FirstName <> UPDATED.FirstName OR 
        Department <> UPDATED.Department OR 
        Salary <> UPDATED.Salary
    )
)
ORDER BY id;

Answer 2

Anonymous

Hello.
If you want to see any changes (if any) you can edit the query this way (assuming, as it appears, the id field is the primary key).

   SELECT
       ORIGINALv1.FirstName
      ,ORIGINALv1.LastName
      ,ORIGINALv1.Department
      ,ORIGINALv1.Salary
      ,UPDATED.Salary
      ,UPDATED.Department
      ,UPDATED.LastName
     FROM dbo.ORIGINALv1
     LEFT OUTER JOIN dbo.UPDATED
       ON UPDATED.id = ORIGINALv1.id

Carlton Patterson 41 Reputation points

2020-09-05T15:31:10.97+00:00

Hi Skireyra,

Thanks for responding, but this isn't exactly what I was expecting.

Share via

How to show Missing Data Using LeftOuterJoin or Any Join Methods

1 additional answer

Your answer