CODEWITHSUNDEEP
sqlmysql
Sinan
Sinan

Reputation: 5980

How to select multiple results for a single row - MySQL

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?

Upvotes: 0

Views: 1114

Answers (3)

onurbaysan
onurbaysan

Reputation: 1258

There is a mysql concat function.

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

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452977

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

Upvotes: 4

Thomas
Thomas

Reputation: 64635

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.

Upvotes: 2

Related Questions