MySQL - error #1055 when joining tables

Question

I have following tables:

game:

+----+-----------------------------------+
| id | title                             |
+----+-----------------------------------+
|  1 | The Witcher                       |
|  2 | The Witcher 2: Assassins of Kings |
|  3 | The Witcher 3: Wild Hunt          |
+----+-----------------------------------+

platform:

+----+------+
| id | name |
+----+------+
|  1 | PC   |
|  2 | MAC  |
|  3 | X360 |
|  4 | PS3  |
|  5 | XONE |
|  6 | PS4  |
+----+------+

game_platform

+----+---------+-------------+
| id | id_game | id_platform |
+----+---------+-------------+
|  1 |       1 |           1 |
|  2 |       1 |           2 |
|  3 |       2 |           1 |
|  4 |       2 |           2 |
|  5 |       2 |           3 |
|  6 |       3 |           1 |
|  7 |       3 |           5 |
|  8 |       3 |           6 |
+----+---------+-------------+

And as a result I want something like this:

    +-----------------------------------+----+-----+------+-----+------+-----+
    | game                              | PC | MAC | X360 | PS3 | XONE | PS4 |
    +-----------------------------------+----+-----+------+-----+------+-----+
    | The Witcher 3: Wild Hunt          |  x |     |      |     |   x  |  x  |
    +-----------------------------------+----+-----+------+-----+------+-----+

The query I'm using is:

SELECT g.title as 'Title',
IF (gp.id_platform = 1, 'x', '') as 'PC',
IF (gp.id_platform = 2, 'x', '') as 'MAC',
IF (gp.id_platform = 3, 'x', '') as 'X360',
IF (gp.id_platform = 4, 'x', '') as 'PS3'
IF (gp.id_platform = 5, 'x', '') as 'XONE'
IF (gp.id_platform = 6, 'x', '') as 'PS4'
FROM game g LEFT JOIN (platform pl, game_platform gp) ON (g.id = gp.id_game and pl.id = gp.id_platform)
WHERE g.id = 1;

And everything is fine, except data is presented like this:

    +-----------------------------------+----+-----+------+-----+------+-----+
    | game                              | PC | MAC | X360 | PS3 | XONE | PS4 |
    +-----------------------------------+----+-----+------+-----+------+-----+
    | The Witcher 3: Wild Hunt          |  x |     |      |     |      |     |
    | The Witcher 3: Wild Hunt          |    |     |      |     |   x  |     |
    | The Witcher 3: Wild Hunt          |    |     |      |     |      |  x  |
    +-----------------------------------+----+-----+------+-----+------+-----+

When I add GROUP BY clause at the end:

SELECT g.title as 'Title',
IF (gp.id_platform = 1, 'x', '') as 'PC',
IF (gp.id_platform = 2, 'x', '') as 'MAC',
IF (gp.id_platform = 3, 'x', '') as 'X360',
IF (gp.id_platform = 4, 'x', '') as 'PS3'
IF (gp.id_platform = 5, 'x', '') as 'XONE'
IF (gp.id_platform = 6, 'x', '') as 'PS4'
FROM game g INNER JOIN (platform pl, game_platform gp) ON (g.id = gp.id_game and pl.id = gp.id_platform)
WHERE g.id = 1
GROUP BY g.title;

I'm getting error:

#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_klimos.gp.id_platform' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

And if I add the gp.id_platform to the GROUP BY clause, I'm getting to the initial result, as if the data was not grouped.

Is there a method to group the data in the table, as I would like to without changing sql_mode=only_full_group_by? I know already what this option means and why it has been introduced. The database I'm using is not a self-hosted one, so I cannot switch it off anyway.

This is called a pivot table and this question has been asked and answered here on SO sooo many times. The linked duplicate topic describes both static and dynamic pivoting within MySQL. However, pls note that performing such transformations in the application logic may be more effective than in SQL. — Shadow, Jun 19 '17 at 10:48

score 1 · Answer 1 · answered Jun 19 '17 at 10:24

Use CASE instead of IF and select MAX value to get desired result

Try this

SELECT g.title as 'Title',
MAX(CASE WHEN gp.id_platform = 1  then  'x' ELSE '' END) as 'PC',
MAX(CASE WHEN gp.id_platform = 2 then  'x' ELSE '' END) as 'MAC',
MAX(CASE WHEN gp.id_platform = 3 then  'x' ELSE '' END) as 'X360',
MAX(CASE WHEN gp.id_platform = 4 then  'x' ELSE '' END) as 'PS3',
MAX(CASE WHEN gp.id_platform = 5 then  'x' ELSE '' END) as 'XONE',
MAX(CASE WHEN gp.id_platform = 6 then  'x' ELSE '' END) as 'PS4'
FROM game g LEFT JOIN (platform pl, game_platform gp) ON (g.id = gp.id_game and pl.id = gp.id_platform)
WHERE g.id = 1

MySQL - error #1055 when joining tables

1 Answers1