I have the following MySQL tables:
Games can have many genres (action, fighting, horror), so they’re in a lookup table with
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
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.