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