vendredi 3 avril 2015

Selecting tuples from a multivalue attribute

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.


Aucun commentaire:

Enregistrer un commentaire