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



Question:

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

game:

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

platform:

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

game_platform:

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?


Solution:1

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  


Solution:2

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.


Solution:3

There is a mysql concat function.

http://www.brainbell.com/tutorials/MySQL/Concatenating_Fields.htm


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