Select * from 'many to many' SQL relationship
- by Rampant Creative Group
I'm still learning SQL and my brain is having a hard time with this one.
Say I have 3 tables:
teams
players
and teams_players as my link table
All I want to do is run a query to get each team and the players on them.
I tried this:
SELECT *
FROM teams
INNER JOIN teams_players
ON teams.id = teams_players.team_id
INNER JOIN players
ON teams_players.player_id = players.id
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?
----------------------------------------- Edit
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.
I have not yet tried the modified SQL statements provided, but I will today and get back to you.
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:
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
