CODEWITHSUNDEEP
mysqlsqlmany-to-many
Benni
Benni

Reputation: 664

SQL select table that contains multiple attributes in a many to many relationship

This is my ER-Model 1ER-Model with multiple to mutliple relationship

I would like to select all the playlists that have multiple labels. For example select all the playlists that have labels with the title "Category-1" and "Category-2" and "Category-3". My goal is to get the playlists that have a list of labels, not only contain one of them but all.

Thank's for helping out!

Upvotes: 1

Views: 460

Answers (1)

O. Jones
O. Jones

Reputation: 108796

Something like this will find all the playlists that have all three of the labels you mention, and possibly more labels.

For this to work, playlist_id and label_id must be primary keys (or unique keys).

SELECT
    playlist.*
FROM
    playlist
    JOIN playlist_label ON playlist.playlist_id = playlist_label.playlist_id
    JOIN label ON playlist_label.label_id = label.label_id
WHERE
    label.title IN('Category-1', 'Category-2', 'Category-3')
GROUP BY
    playlist.playlist_id
HAVING
    COUNT(*) = 3

I'm guessing at the way your ER tool converts your logical model to a physical model.

Upvotes: 2

Related Questions