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:
x
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 🙁
Advertisement
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