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 🙁
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