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:
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
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.
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
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
Gender (which would result in a many-to-many cardinality of results).