SQL query to group results on matching record ID

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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 Reply