0

I have the following MySQL tables:

  • games
  • genres
  • game_genres

Games can have many genres (action, fighting, horror), so they're in a lookup table with game_id and genre_id. When I pull in my list of games, I want to combine these tables and get the text genre name into a comma separated column. I'm able to GROUP_CONCAT the genres from the lookup table into a column, but struggling to make those join into the plain text version. So if a game called "Street Fighter" was associated with the Fighting and Action genres, it would pull up a row with an aliased column name of genres and would be Street Fighter, Action. I'm also using "themes" in the same manner as genres, but I'm assuming whatever method will help me do what I need to genres will work for themes too.

Here is the query I'm currently using:

SELECT
    games.*,
    GROUP_CONCAT(DISTINCT genre.genre_id) genres,
    GROUP_CONCAT(DISTINCT theme.theme_id) themes
FROM
    retro_games games
LEFT JOIN retro_game_genres genre ON games.game_id=genre.game_id
LEFT JOIN retro_game_themes theme ON games.game_id=theme.game_id
    WHERE games.game_id='1375'
    GROUP BY games.game_id
1

So I finally got this to work by using the following query:

SELECT
    games.*,
    games.game_id game_u_id,
    GROUP_CONCAT(DISTINCT genres.genre_name) genre_names,
    GROUP_CONCAT(DISTINCT themes.theme_name) theme_names
FROM
    retro_games games
LEFT JOIN retro_game_genres all_genres ON games.game_id=all_genres.game_id
LEFT JOIN retro_game_themes all_themes ON games.game_id=all_themes.game_id
LEFT JOIN retro_genres genres ON all_genres.genre_id=genres.genre_id
LEFT JOIN retro_themes themes ON all_themes.theme_id=themes.theme_id
    WHERE games.game_id='1375'
GROUP BY games.game_id

Wondering if this is the most efficient way of doing it though, or is there another method that would faster? It's fine for my one row of 1375 but it will be eventually used on thousands of rows when they view all games.

| improve this answer | |
  • Have you managed to create two accounts here? If so, you can follow these instructions to merge them. – Andriy M Oct 4 '15 at 18:10
  • answer looks like an additional question. Create a new question for that part. – Thronk Oct 4 '15 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.