Choose * from many-to-many SQL

Question

I'm still learning SQL, and my brain has a hard time dealing with it.

Let's 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 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 he returned a separate row for each player in each team. JOIN the right way to do this or should I do something else?

----------------------------------------- Edit

Okay, so from what I hear, this is not necessarily a bad way to do it. I just have to group the data by command while I execute my loop.

I have not tried modified SQL statements yet, but I will get back to you today.

To answer the question about the structure, I think I did not think about the returned row structure, which is part of what leads to my confusion. In this specific case, each team is limited to 4 players (or less), so I assume that the structure that is useful to me looks something like this:

 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 

Answer 1

Ok, this is an assumption, but you want the team and the list of players in the team on the same line

What about

 SELECT t.id, GROUP_CONCAT(p.id) FROM teams t INNER JOIN teams_players tp ON t.id = tp.team_id INNER JOIN players p ON tp.player_id = p.id GROUP BY t.id 

The guide can help http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_group-concat

Answer 2

MySQL will return the data in a table format, so think about how you will return the list of players and teams, including the group that each player is associated with, in one table / result set without returning a separate row for each player to the team?

Your request should return:

 team.id, team.name, player.id, player.name 1 team1 1 player1 1 team1 2 player2 2 team2 3 player3 2 team2 4 player4 

If you want something like

 team.id, team.name 1 team1 2 team2 player.id, player.name, team.id 1 player1 1 2 player2 1 3 player3 2 4 player4 2 

Which has two separate result sets, then you will need to make two queries. If you need a different set of results for each team, you will need to make an additional query for each team.

However, I do not see anything too wrong in your initial request, except that it gives you a lot of duplicate data in exchange for the fact that you do not need to make several requests.