3

I have two DB2 tables.

Table 1, called EmployeeFile

EmployeeFile has the following columns: EMP_ID, FNAME, LNAME, DOB

The data looks like this:

   EMP_ID, FNAME, LNAME, DOB
 - GB2342, John,  Smith, 1970-12-10
 - L3243C, Jane,  Jones, 1969-11-09

Table 2, called OriginalData

OriginalData has the following columns: INTERNAL_ID, FNAME, LNAME, DOB.

The data looks like this:

   INTERNAL_ID, FNAME, LNAME,    DOB
 - 6575678588,  Jane,  Jones,    1969-11-09
 - 1232145564,  Rob,   Mitchell, 1968-10-08
 - 3452345345,  John,  Smith,    1970-12-10
 - 6786478568,  John,  Smith,    1970-12-10

I want to show all rows of EmployeeFile for which the (FNAME, LNAME, DOB) combination occurs only once in OriginalData.

For example, if I ran the query on the example dataset shown above, it should return just the following row:

  • Jane, Jones, 1969-11-09

I don't want 'John Smith' because although he exists in EmployeeFile, his (FNAME, LNAME, DOB) combination occurs twice in OriginalData.

I don't want 'Rob Mitchell' because he doesn't exist in EmployeeFile.

Question

How do I do this?

The only way I can think of is immensely ugly: Concatenate (FNAME, LNAME, DOB) (with some delimiter like ~) from both tables, do a join on that column, do a group by having count(that concatenated column) = 1, then do another select on EmployeeFile, selecting only those rows where the (FNAME, LNAME, DOB) match the parsed substrings of that concatenated string!

  • You don't need any concatenation as you can join tables on multiple columns; don't see why you need "another select on EmployeeFile". This leaves you with a group by, which is not ugly at all. – mustaccio May 9 '18 at 18:42
  • You could use a correlated EXISTS of course (without a GROUP BY) if you wanted. GROUP BY solution seems straight forward though. – ypercubeᵀᴹ May 9 '18 at 19:00
  • @ypercubeᵀᴹ "combination occurs only once" -- how would you formulate the exists for this? You'd need a not exists too, which would probably be pretty inefficient. – mustaccio May 9 '18 at 19:22
  • @mustaccio exactly. With a not exists inside the exists subquery ;) – ypercubeᵀᴹ May 9 '18 at 19:23
  • 1
    @mustaccio you are probably right about performanc but the devil ia always in the details. If the EmployeeFile table is very small and the OriginalData huge, I'd prefer a doubly correllated EXISTS/NOT EXISTS query (that would be evaluated only for every row of the small table) than a GROUP BY on the huge table (that the straightforward solution might do). – ypercubeᵀᴹ May 9 '18 at 19:31
5

Pretty straightforward:

select e.* 
from EmployeeFile e
inner join
(
  select FNAME, LNAME, DOB
  from OriginalData
  group by FNAME, LNAME, DOB
  having count(1) = 1
) o
on (e.FNAME, e.LNAME, e.DOB) = (o.FNAME, o.LNAME, o.DOB)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy