Merge table with genre/category table seperated by comma from linking table

Question

I'm making a gif database and I want to store every gif by genre in my database, I'm using 3 tables, genres, gifs and gifs_genres, genres stores all the genres that are in the system, gifs are the actual gifs and their storage location, and gifs_genres stores what gifs have what genres

They look like this:
Genres:

gifs:

gifs_genres:

and I want to be able to search with multiple genres and find only the results with ALL genres

the desired results: (made in exel to showcase)
Filtering for genre 36 (cute)

and If I filter for 38 and 36 (cartoon and cute)

what I'm using right now:

USE gifapi;
SELECT title, file, genre
FROM gifs
RIGHT JOIN gifs_genres ON  gifs.id = gifs_genres.gif_id
RIGHT JOIN genres ON gifs_genres.genre_id = genres.id
WHERE gifs_genres.genre_id = 36

I'm not very experienced with SQL so I am unable to figure this out. All I've done with sql before is Select *, and UPDATE

The reason why I want a comma separated value is only to show the user what genre's the gif is, I will not store the table that is created.

See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad, and if still struggling, see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Dec 09 '19 at 14:12

score 1 · Accepted Answer · answered Dec 09 '19 at 14:16

You can join, aggregate, and filter with a having clause:

select
    i.title,
    i.file,
    group_concat(e.genre order by e.id) genres
from gifs i
inner join gives_genre g on g.gif_id = i.id
inner join genres e on e.id = g.genre_id
group by i.title, i.file
having
    max(e.id = 36) = 1
    and max(e.id = 38) = 1

Merge table with genre/category table seperated by comma from linking table

1 Answers1