Tutorial :How to select multiple results for a single row - MySQL


I have 3 tables: game, platform, and game_platform.


id    name               desc  ----  ------------       ---------  1     Modern Warfare...  Awesome game......  2     ...                ...  3     ...                ...  


id    name  ----  ------------  1     pc  2     ps3  3     ...  


game_id    platform_id  --------   -----------  1          1  1          2  ...        ...  

What I want is to get a row containing data from the game table and also all the platforms for that game.

However with a regular query I get multiple rows for games with more than one platform. Is there a way to get them on a single row?


You don't provide an example of desired output. If you want all the platforms brought back in one column this will do it.

   SELECT g.id, g.name, g.desc, GROUP_CONCAT(P.name) AS platforms      from game g      JOIN game_platform gp ON gp.game_id = g.id      JOIN platform P ON gp.platform_id = p.id      GROUP BY g.id, g.name, g.desc  


Select G.Id, G.name      , Min( Case When P.Id = 1 Then P.Name End ) As Platform1      , Min( Case When P.Id = 2 Then P.Name End ) As Platform2      , Min( Case When P.Id = 3 Then P.Name End ) As Platform3  From game As G      Join game_platform As GP          On GP.game_id = G.id      Join platform As P          On P.Id = GP.platform_id  Group By G.Id, G.name  

If you want to dynamically determine the platform columns, then you need to build the query dynamically in your calling or middle-tier code.


There is a mysql concat function.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »