0

I am having trouble designing a query. I have a list of PersonIds for records in a Persons table. I would like to have a single query that takes this list of PersonIds and returns other records in the table that match each input person's date of birth and gender, aggregated by the InputPersonId, so each returned record would have:

  • InputPersonId
  • MatchedPersonId
  • DateOfBirth
  • Gender

Sample data:

Persons table

PersonId DateOfBirth Gender
1        1/1/1950      M
2        1/2/1950      M
3        1/1/1950      M
4        1/1/1950      F
5        1/1/1950      F
6        1/2/1950      F

An example is that I might want all of the records the match DateOfBirth and Gender for PersonIds 1 and 4. I would expect the output to be

InputPersonId MatchedPersonId DateOfBirth Gender
1               1              1/1/1950      M
1               3              1/1/1950      M
4               4              1/1/1950      F
4               5              1/1/1950      F
0

1 Answer 1

0

You should be able to accomplish your goal with something like the following:

SELECT DISTINCT
    InputPersons.PersonId AS InputPersonId, 
    MatchedPersons.PersonId AS MatchedPersonId, 
    MatchedPersons.DateOfBirth, 
    MatchedPersons.Gender
FROM Persons AS InputPersons
INNER JOIN Persons AS MatchedPersons
    ON InputPersons.DateOfBirth = MatchedPersons.DateOfBirth
    AND InputPersons.Gender = MatchedPersons.Gender
WHERE InputPersons.PersonId IN (1, 4);

So let's examine what this query is doing. Starting with the FROM clause, we begin with the Persons table which we alias (like a local nickname / reference) as InputPersons and then INNER JOIN it to itself (aliased as MatchedPersons) to only return matching records by DateOfBirth and Gender (hence the use of an INNER instead of OUTER JOIN which will ensure to filter out anything that doesn't match by this clause).

Then in the WHERE clause is how we filter down the InputPersons to only the specific PersonIds we care about.

Finally, if you observe the SELECT list, you'll notice we choose the appropriate fields we need from their correlating instances of our tables. Also note the use of DISTINCT in the SELECT clause to remove any duplicate results which can occur should two different Persons have the same DateOfBirth and Gender (which would result in a many-to-many cardinality of results).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.