Tweeted twitter.com/StackDBAs/status/994297363853430787
3 added 127 characters in body
source | link

I have two DB2 tables.

Table 1, called EmployeeFile, which

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

The data it retains looks like this:

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

Table 2, called OriginalData, with

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

The data it encloses looks like this:

  • 6575678588, Jane, Jones, 1969-11-09
  • 1232145564, Rob, Mitchell, 1968-10-08
  • 3452345345, John, Smith, 1970-12-10
  • 6786478568, John, Smith, 1970-12-10
   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!

I have two DB2 tables.

Table 1, called EmployeeFile, which has the columns EMP_ID, FNAME, LNAME and DOB.

The data it retains looks like this:

  • GB2342, John, Smith, 1970-12-10
  • L3243C, Jane, Jones, 1969-11-09

Table 2, called OriginalData, with the columns INTERNAL_ID, FNAME, LNAME and DOB.

The data it encloses looks like this:

  • 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!

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!

2 Modified formatting and made a few rewordings.
source | link

I have two DB2 tables.

Table 1, called EmployeeFile

EmployeeFileEmployeeFile, which has the following columns: EMP_ID, FNAME EMP_ID, LNAMEFNAME, DOBLNAME and DOB.

The data it retains looks like this:

  • GB2342, John, Smith, 1970-12-10
  • L3243C, Jane, Jones, 1969-11-09

Table 2, called OriginalData

OriginalData hasOriginalData, with the following columns: INTERNAL_ID, FNAME INTERNAL_ID, LNAMEFNAME, DOBLNAME and DOB.

The data it encloses looks like this:

  • 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 EmployeeFileEmployeeFile for which the FN, LN, DOB(FNAME, LNAME, DOB) combination occurs only once in OriginalDataOriginalData.

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'John Smith' because although he exists in EmployeeFileEmployeeFile, his FN-LN-DOB(FNAME, LNAME, DOB) combination occurs twice in OriginalDataOriginalData.

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

Question

How do I do this?

The only way I can think of is immensely ugly: Concatenate FN, LN, DOB (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) = 1count(that concatenated column) = 1, then do another select on EmployeeFileEmployeeFile, selecting only those rows where the FN, LN, DOB(FNAME, LNAME, DOB) match the parsed substrings of that concatenated string!

I have two DB2 tables.

Table 1, called EmployeeFile

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

The data looks like this:

  • 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:

  • 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 FN, LN, 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 FN-LN-DOB combination occurs twice in OriginalData.

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

How do I do this?

The only way I can think of is immensely ugly: Concatenate FN, LN, 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 FN, LN, DOB match the parsed substrings of that concatenated string!

I have two DB2 tables.

Table 1, called EmployeeFile, which has the columns EMP_ID, FNAME, LNAME and DOB.

The data it retains looks like this:

  • GB2342, John, Smith, 1970-12-10
  • L3243C, Jane, Jones, 1969-11-09

Table 2, called OriginalData, with the columns INTERNAL_ID, FNAME, LNAME and DOB.

The data it encloses looks like this:

  • 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!

1
source | link

Finding rows that are not duplicate

I have two DB2 tables.

Table 1, called EmployeeFile

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

The data looks like this:

  • 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:

  • 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 FN, LN, 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 FN-LN-DOB combination occurs twice in OriginalData.

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

How do I do this?

The only way I can think of is immensely ugly: Concatenate FN, LN, 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 FN, LN, DOB match the parsed substrings of that concatenated string!