Selecting tuples from a multivalue attribute

Question

I'm trying to get a query for the following example, where I need to select all people who have played for the same team (or the same and others) as some person. For example

person  teamName
1       maple leafs
1       rangers
2       red wings
3       rangers
3       avalanche
3       maple leafs

I'm trying to create a query that says "find all players who have played on the same teams as player 1". In the example above, it should return players 1 and 3. I've got something working, ie

select person from teams where teamName = 'maple leafs' intersect select person from teams where teamName = 'rangers';

However it's too hard coded (player might get sent to another team). I can get a list of players who have been on a team of players 1 with the following

create table temp as select teamName from teams where person = 1;
select * from temp join teams on temp.teamName = teams.teamName;

But I don't know then how to extract people who are on all the same teams as player 1. I've tried group by and having clauses, but when I group by person, any teams more than the first are lost, so I'm a bit stuck.

Any help is appreciated.

Answer 1

You can generalize the query like this:

select person
from yourtablename
where teamname in (select teamname from yourtablename where person = 1)
group by person
having count(*) = (select count(*) from yourtablename where person = 1);

The first sub-select limits the teams to just those that person 1 has been on. But that still includes people that have been on at least one of the teams but not all. Then the HAVING clause ensures that the people returned by the query have the exact number of teams as person 1, meaning that they've been on all of the same teams.