title: Complex SQL query with multiple tables and relations
body: <p>In this Query, I have to list pair of players with their playerID and playerName who play for the exact same teams.If a player plays for 3 teams, the other has to play for exact same 3 teams. No less, no more. If two players currently do not play for any team, they should also be included. The query should return (playerID1, playername1, playerID2, playerName2) with no repetition such as if player 1 info comes before player 2, there should not be another tuple with player 2 info coming before player 1.</p>
  
  <p>For example if player A plays for yankees and redsox, and player b plays for Yankees, Red Sox, and Dodgers I should not get them. They both have to play for Yankees, and Red Sox and no one else. Right now this query finds answer if players play for any same team.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So I should get this as answer-</p>
  
  <pre><code> 2, Allen, 3, Pierce 
   4, Garnett, 5, Perkins
  </code></pre>
  
  <p>.</p>
  
  <p>2, Allen, 3 Pierce is an snwer because both play for exclusively CELTICS and PATRIOTS
   4, Garnett, 5, Perkins iss an answer because both players play for no teams which should be in output.</p>
  
  <p>Right now the Query I have is</p>
  
  <pre><code>SELECT p1.PLAYERID, 
         f1.PLAYERNAME, 
         p2.PLAYERID, 
         f2.PLAYERNAME 
  FROM   PLAYER f1, 
         PLAYER f2, 
         PLAYS p1 
         FULL OUTER JOIN PLAYS p2 
                      ON p1.PLAYERID &lt; p2.PLAYERID 
                         AND p1.TEAMID = p2.TEAMID 
  GROUP  BY p1.PLAYERID, 
            f1.PLAYERID, 
            p2.PLAYERID, 
            f2.PLAYERID 
  HAVING Count(p1.PLAYERID) = Count(*) 
         AND Count(p2.PLAYERID) = Count(*) 
         AND p1.PLAYERID = f1.PLAYERID 
         AND p2.PLAYERID = f2.PLAYERID; 
  </code></pre>
  
  <p>I am not 100% sure but I think this finds players who play for the same team but I want to find out players who play for the exclusively all same TEAMS as explained above</p>
  
  <p>I am stuck on how to approach it after this. Any hints on how to approach this problem. Thanks for your time. </p>