title: Select * from 'many to many' SQL relationship
body: <p>I'm still learning SQL and my brain is having a hard time with this one.</p>
  
  <p>Say I have 3 tables:</p>
  
  <pre><code>teams
  players
  </code></pre>
  
  <p>and <code>teams_players</code> as my link table</p>
  
  <p>All I want to do is run a query to get each team and the players on them.</p>
  
  <p>I tried this: </p>
  
  <pre><code>SELECT * 
  FROM teams 
  INNER JOIN teams_players 
      ON teams.id = teams_players.team_id 
  INNER JOIN players 
      ON teams_players.player_id = players.id
  </code></pre>
  
  <p>But it returned a separate row for each player on each team. Is JOIN the right way to do it or should I be doing something else?</p>
  
  <p>----------------------------------------- Edit</p>
  
  <p>Ok, so from what I'm hearing, this isn't necessarily a bad way to do it. I'll just have to group the data by team while I'm doing my loop.</p>
  
  <p>I have not yet tried the modified SQL statements provided, but I will today and get back to you.</p>
  
  <p>To answer the question about structure - I guess I wasn't thinking about the returned row structure which is part of what lead to my confusion. In this particular case, each team is limited to 4 players (or less) so I guess the structure that would be helpful to me is something like the following:</p>
  
  <pre><code>teams.id, teams.name, players.id, players.name, players.id, players.name, players.id, players.name, players.id, players.name,
  1         Team ABC    1           Jim           2           Bob           3            Ned          4             Roy
  2         Team XYZ    2           Bob           3           Ned           5            Ralph        6             Tom
  </code></pre>