SQL Group rows in left join into one

i try create query with left join when i combine multiple rows into one. I try with GROUP_CONTENT function but when i try use it my db server is going down. I use MariaDB 10.3.17. I have tables like:

Games:

game_id game_name
1       Test
2       Stack
3       Other

data_developers:

dev_id dev_name
1      Electronic Arts
2      BioWare
3      2K Games

game_developers

developer_id game_id
1            1
2            1
2            3

Result i want:

game_id game_name devs
1       Test      Electonics Arts, BioWare
2       Stack     2K Games

my two sql (but didnt work)

SELECT games.*, GROUP_CONCAT(data_developers.dev_name) 
FROM games 
LEFT JOIN game_developers ON game_developers.game_id = games.game_id
LEFT JOIN data_developers ON data_developers.dev_id = game_developers.dev_id
LIMIT 500

and second query

SELECT games.*
FROM games
LEFT JOIN game_developers ON game_developers.game_id = games.game_id
LEFT JOIN 
(SELECT GROUP_CONCAT(data_developers.developer_name) as developers, 
data_developers.developer_id FROM data_developers) x 
ON x.developer_id = game_developers.developer_id

But of course, also dont work 🙁

Answer

Your query should be something like below-

SELECT A.game_id,B.Game_name,GROUP_CONCAT(C.dev_name)
FROM game_developers A
INNER JOIN Games B ON A.game_id = B.game_id
INNER JOIN data_developers C ON A.developer_id = C.dev_id
GROUP BY A.game_id,B.Game_name

Advertisement

Answer