SQL query to group results on matching record ID

by

The question:

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

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).


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment