I have three tables where I store actual person data (person), teams (team) and entries (athlete). The schema of the three tables is:
In each team there might be two or more athletes.
I'm trying to create a query to produce the most frequent pairs, meaning people who play in teams of two. I came up with the following query:
SELECT p1.surname, p1.name, p2.surname, p2.name, COUNT(*) AS freq
FROM person p1, athlete a1, person p2, athlete a2
WHERE
p1.id = a1.person_id AND
p2.id = a2.person_id AND
a1.team_id = a2.team_id AND
a1.team_id IN
( SELECT id
FROM team, athlete
WHERE team.id = athlete.team_id
GROUP BY team.id
HAVING COUNT(*) = 2 )
GROUP BY p1.id
ORDER BY freq DESC
Obviously this is a resource consuming query. Is there a way to improve it?
