postgresql - Complex SQL query with multiple tables and relations -

in query, have list pair of players playerid , playername play exact same teams.if player plays 3 teams, other has play exact same 3 teams. no less, no more. if 2 players not play team, should included. query should return (playerid1, playername1, playerid2, playername2) no repetition such if player 1 info comes before player 2, there should not tuple player 2 info coming before player 1.

for example if player plays yankees , redsox, , player b plays yankees, red sox, , dodgers should not them. both have play yankees, , red sox , no 1 else. right query finds answer if players play same team.

tables: player(playerid: integer, playername: string) team(teamid: integer, teamname: string, sport: string) plays(playerid: integer, teamid: integer)  example data: player     playerid    playername 1           rondo 2           allen 3           pierce 4           garnett 5           perkins  team       teamid     teamname       sport 1          celtics        basketball 2          lakers         basketball 3          patriots       football 4          red sox        baseball 5          bulls          basketball  plays playerid    teamid 1           1 1           2 1           3 2           1 2           3 3           1 3           3 

so should answer-

 2, allen, 3, pierce   4, garnett, 5, perkins 

.

2, allen, 3 pierce snwer because both play exclusively celtics , patriots 4, garnett, 5, perkins iss answer because both players play no teams should in output.

right query have is

select p1.playerid,         f1.playername,         p2.playerid,         f2.playername    player f1,         player f2,         plays p1         full outer join plays p2                      on p1.playerid < p2.playerid                         , p1.teamid = p2.teamid  group  p1.playerid,            f1.playerid,            p2.playerid,            f2.playerid  having count(p1.playerid) = count(*)         , count(p2.playerid) = count(*)         , p1.playerid = f1.playerid         , p2.playerid = f2.playerid;  

i not 100% sure think finds players play same team want find out players play exclusively same teams explained above

i stuck on how approach after this. hints on how approach problem. time.

i believe query want:

select array_agg(players), player_teams (   select distinct t1.t1player players, t1.player_teams   (     select       p.playerid t1id,       concat(p.playerid,':', p.playername, ' ') t1player,       array_agg(pl.teamid order pl.teamid) player_teams     player p     left join plays pl on p.playerid = pl.playerid     group p.playerid, p.playername   ) t1 inner join (   select     p.playerid t2id,     array_agg(pl.teamid order pl.teamid) player_teams   player p   left join plays pl on p.playerid = pl.playerid   group p.playerid, p.playername ) t2 on t1.player_teams=t2.player_teams , t1.t1id <> t2.t2id ) innerquery group player_teams 

result: players               player_teams 2:allen,3:pierce      1,3 4:garnett,5:perkins 

it uses array_agg on teamid each player in plays match players exact same team configuration. included column teams example, can removed without affecting results long isn't removed group clause.

sql fiddle example.tested postgesql 9.2.4

edit: fixed error duplicated rows.