# 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 `PersonId`s 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 🙂