Delete matching records between table A and B

Transact-SQL
1

Hi,

I have two tables that I need to compare. Here are the two tables and the detail:

First select statement from NoLongerEmployee table:
SELECT Employee_Name, Hire_Date, Department_ID
FROM NoLongerEmployee

and below is the result set of the above query:

Employee_Name Hire_Date Department_ID
Barry Small 04/20/2013 HLPDSK345
Melinda Tan 02/03/2012 MGR455
Jose Runo 11/16/2013 CUSTSRV554
Barry Small 04/12/2015 APPDEV234
Christine Lie 03/21/2013 QC234
Nichole James 07/08/2013 SVC323
Vicki Johnson 06/24/2014 MGR776
Bryan Kosh 01/16/2015 IT334

Second select statement from StillEmployee table:
SELECT Employee_Name, Hire_Date, Department_ID
FROM StillEmployee

and below is the result set of the above query:

Employee_Name Hire_Date Department_ID
Barry Small 04/20/2013 HLPDSK345
Melinda Tan 02/03/2012 MGR455
Jose Runo 11/16/2013 CUSTSRV554
Barry Small 04/12/2015 APPDEV234
Christine Lie 03/21/2013 QC234
Nichole James 07/08/2013 SVC323
Melinda Tan 05/05/2016 MGR556

What I am trying to achieve is to compare between table NoLongerEmployee and StillEmployee to match all those three fields row by row. I want to get the result set in the NoLongerEmployee table where the records are not listed in StillEmployee table based on matching fields row by row.

The result set I want to see from table NoLongerEmployee should like below:

Employee_Name Hire_Date Department_ID
Vicki Johnson 06/24/2014 MGR776
Because - (No matching on the Employee_Name. Hire_Date and Department_ID fields between NoLongerEmployee and StillEmployee)

Bryan Kosh 01/16/2015 IT334
Because - (No matching on the Employee_Name. Hire_Date and Department_ID fields between NoLongerEmployee and StillEmployee)

Anyone knows how do I achieve this?

2 
SELECT Employee_Name, Hire_Date, Department_ID
FROM NoLongerEmployee AS N
WHERE NOT EXISTS
(
    SELECT *
    FROM StillEmployee AS S
    WHERE S.Employee_Name = N.Employee_Name
          AND S.Hire_Date = N.Hire_Date
          AND S.Department_ID = N.Department_ID
)

it would be better to compare using some sort of Employee ID, rather than Employee_Name, because it is likely that Employee_Name could be ambiguous.

1 Like
3

This is great..thanks Kristen

4

@Mel,
Here's another solution:

DECLARE @EmpTerminated table
   ( EmployeeName varchar(100)
   , HireDate date
   , DeptId varchar(20)
   );
INSERT @EmpTerminated
       (EmployeeName, HireDate, DeptId)
VALUES ( 'Barry Small'  , '04/20/2013', 'HLPDSK345' )
     , ( 'Melinda Tan'  , '02/03/2012', 'MGR455' )
     , ( 'Jose Runo'    , '11/16/2013', 'CUSTSRV554' )
     , ( 'Barry Small'  , '04/12/2015', 'APPDEV234' )
     , ( 'Christine Lie', '03/21/2013', 'QC234' )
     , ( 'Nichole James', '07/08/2013', 'SVC323' )
     , ( 'Vicki Johnson', '06/24/2014', 'MGR776' )
     , ( 'Bryan Kosh'   , '01/16/2015', 'IT334' );  
  
DECLARE @EmpWorking table
   ( EmployeeName varchar(100)
   , HireDate date
   , DeptId varchar(20)
   );
INSERT @EmpWorking
        (EmployeeName, HireDate, DeptId)
VALUES ( 'Barry Small'  , '04/20/2013', 'HLPDSK345' )        
    ,  ( 'Melinda Tan'  , '02/03/2012', 'MGR455' )
    ,  ( 'Jose Runo'    , '11/16/2013', 'CUSTSRV554' )
    ,  ( 'Barry Small'  , '04/12/2015', 'APPDEV234' )
    ,  ( 'Christine Lie', '03/21/2013', 'QC234' )
    ,  ( 'Nichole James', '07/08/2013', 'SVC323' )
    ,  ( 'Melinda Tan'  , '05/05/2016', 'MGR' );
SELECT
      et.EmployeeName
    , et.HireDate
    , et.DeptId
FROM
      @EmpTerminated et
LEFT  JOIN 
      @EmpWorking ew 
   ON ew.EmployeeName = et.EmployeeName
  AND ew.EmployeeName = et.EmployeeName
  AND ew.HireDate = et.HireDate
WHERE ew.EmployeeName IS NULL;

1 Like
5

That LEFT OUTER JOIN solution is the one I normally use. I like the fact that I can easily toggle it to an Inner Join whilst fiddling about - if I want to see what matches, rather than what doesn't, and keeping the code the "same" (to reduce bugs) if I have some UpSerts comprising INSERT statements followed by UPDATEs - jsut toggle the OUTER to INNER and not have completely different JOIN-for-one and EXISTS-for-the-other code - more chance of bugs.

I tend not to post the Outer Join method in code samples as I assume that's its harder to grasp (for newbies) - but maybe that isn't the case? I definitely think its more flexible, so perhaps I should recommend it as first-choice?

There again, I might be over thinking the whole thing ... as usual!!

1 Like
6

Thank you, jotorre_riversidedpss.org. Great!

7

Thank you again Kristen.