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