Select a movie and all its genres on one row

user_14802364 0
Viewed 40

So I have 3 tables: shows(pk: id), show_genres(pk: id, fk: show_id, genre_id), genres(pk: id).

A show can have multiple genres and I need to take them all.

My table looks for a show looks like this now:

title year genres
Sherlock 2016 Crime
Sherlock 2016 Documentary

For every genre of a show it makes another row, instead of adding only the second genre.

My query looks like this:

SELECT title, year, genres.name genres
FROM shows
LEFT JOIN show_genres ON shows.id=show_genres.show_id
LEFT JOIN genres ON show_genres.genre_id=genres.id

I have tried with SELECT DISTINCT and other suggestions from other questions. Also from my research I did not find anything that could work.

Any suggestions about what I should try or what do I do wrong ?

postgresql sql
1 Answers

You should use STRING_AGG alone with GROUP BY to get one row for each movie with all genres aggregated in string. Your query would be like this

SELECT 
    title, 
    year, 
    STRING_AGG(genres.name, ',') genres 
FROM shows 
LEFT JOIN show_genres ON shows.id = show_genres.show_id 
LEFT JOIN genres ON show_genres.genre_id = genres.id
GROUP BY title, year
user_58768030
Related