Selecting data from multiple tables and placing it into a single row-CodePudding

I am trying to generate a selection of table columns which is proving difficult - I am using phpmyadmin tool

I have a Players table and I have a teams table - My objective is to display each team in a single row however i want to add to that row every player_ign who obtains a tb_players.player_teamid = tb_teams.team_id.

additionally, for example we have 2 players with the same player_teamId, I want to have those clomun headers read as player1, player2, instead of having the same duplicated player_ign column we see in the expected result.

I have added an example of my expected result at the bottom

This is what I have tried - according to my logic, but sadly getting nowhere. I understand that this doesnt make sense because the second SELECT returns 2 Rows and I need to have it as 2 columns with 1 row - but I am struggling with that logic

SELECT tb_teams.team_logo, tb_teams.team_name, tb_teams.team_abreviation, (SELECT 
tb_players.player_ign FROM tb_players WHERE tb_players.player_id =tb_teams.team_id)
FROM tb_teams
INNER JOIN tb_players
ON tb_teams.team_id = tb_players.player_id

This is my Players table (tb_players)

This is my Teams Table (tb_teams)

This is my expected result which I am struggling to get to

CodePudding user response：

There are no such things as dynamic columns. The number of columns should be equal for all records. So you cannot do this. You can get the players in one column, though.

Try this instead:

SELECT tb_teams.team_logo, tb_teams.team_name, tb_teams.team_abreviation, GROUP_CONCAT(tb_players.player_ign) AS player_igns
FROM tb_teams
    INNER JOIN tb_players ON tb_teams.team_id = tb_players.player_id
GROUP BY tb_teams.team_id

CodePudding user response：

If you know maximum amount of players you can get in result, you can make columns for maximum result set and get desired list. All unused fields wil be NULL, so you will need to check if not NULL before usage.

SELECT tb_teams.team_logo, tb_teams.team_name, tb_teams.team_abreviation, 
substring_index(player_ign, ',', 1) as player1,
(case when indexnum >= 2 then substring_index(substring_index(player_ign, ',', 2), ',', -1) end) as player2,
(case when indexnum >= 3 then substring_index(substring_index(player_ign, ',', 3), ',', -1) end) as player3,
(case when indexnum >= 4 then substring_index(substring_index(player_ign, ',', 4), ',', -1) end) as player4,
(case when indexnum >= 5 then substring_index(substring_index(player_ign, ',', 5), ',', -1) end) as player5,
(case when indexnum >= 6 then substring_index(substring_index(player_ign, ',', 6), ',', -1) end) as player6,
(case when indexnum >= 7 then substring_index(substring_index(player_ign, ',', 7), ',', -1) end) as player7,
(case when indexnum >= 8 then substring_index(substring_index(player_ign, ',', 8), ',', -1) end) as player8,
(case when indexnum >= 9 then substring_index(substring_index(player_ign, ',', 9), ',', -1) end) as player9
FROM (
SELECT tb_teams.team_logo, tb_teams.team_name, tb_teams.team_abreviation, GROUP_CONCAT(tb_players.player_ign) AS player_igns, count(*) as indexnum
FROM tb_teams
    INNER JOIN tb_players ON tb_teams.team_id = tb_players.player_id
GROUP BY tb_teams.team_id
) tttmp;