MaryCoding
MaryCoding

Reputation: 664

Display one row for value with multiple categories

I have a table named games. Each game belongs to one or more categories. I have a query that uses GROUP BY category_id but returns not the desired result. For the example below, COD belongs to three category_id but the query only shows for that specific game and omits other games with the same category_id. How can I achieve the below desired query?

Query:

SELECT *
FROM games
GROUP BY category_id

Desired Result:

+----------------+--------------------------+
|   game_name    |       category_id        |
+----------------+--------------------------+
| COD            | adventure, fighting, fps |
| Tekken         | fighting                 |
| Need for Speed | racing                   |
+----------------+--------------------------+

SqlFiddle

Upvotes: 2

Views: 442

Answers (1)

4EACH
4EACH

Reputation: 2197

Use GROUP_CONCAT function

SELECT
 game_name, GROUP_CONCAT(category_id) categories
FROM
  games
 GROUP BY 
 game_name;

Good luck!

Upvotes: 3

Related Questions