How to query many-to-many relation with features table (AND condition)

user_6808559 0
Viewed 31

I guess this is a common setting, but as I don't do that much SQL work, I can't get my head around this one... So, I've got a bunch of songs that have certain features (style of music, mood etc.) and I would like to select songs that are attributed some of these features (e. g. songs that are happy and euphoric).

SONG
+----+----------+
| id |    title |
+----+----------+
|  1 |  song #1 |
+----+----------+
|  2 |  song #2 |
+----+----------+

FEATURE
+----+-------+----------+
| id |  name |    value |
+----+-------+----------+
|  1 |  mood |      sad |
+----+-------+----------+
|  2 |  mood |    happy |
+----+-------+----------+
|  3 |  mood | euphoric |
+----+-------+----------+
|  4 | style |     rock |
+----+-------+----------+
|  5 | style |     jazz |
+----+-------+----------+

SONG_FEATURE
+---------+------------+
| song_id | feature_id |
+---------+------------+
|       1 |          1 |
+---------+------------+
|       2 |          1 |
+---------+------------+
|       2 |          2 |
+---------+------------+

I would like to select all the songs that have certain features with an AND condition. I would use this query for the OR-case.

SELECT 
    s.*,
    f.*
FROM
    song_feature sf
        LEFT JOIN song s ON s.id = sf.song_id
        LEFT JOIN feature f ON f.id = sf.feature_id
WHERE
    (
        f.name = 'style'
        AND f.value = 'pop'
    )
    OR /* <-- this works, but I would like an AND condition */
    (
        f.name = 'style'
        AND f.value = 'pop'
    )
GROUP BY sf.song_id;

But this obviously does not work for the AND condition, so I guess I'm on the wrong track here... Any hints will be greatly appreciated.

many-to-many mysql sql
1 Answers

You can do it with aggregation, if you filter the resultset of the joins and set the condition in the HAVING clause:

SELECT s.id, s.title
FROM SONG s
INNER JOIN SONG_FEATURE sf ON sf.song_id = s.id
INNER JOIN FEATURE f ON f.id = sf.feature_id
WHERE (f.name, f.value) IN (('mood', 'sad'), ('mood', 'happy'))
GROUP BY s.id, s.title
HAVING COUNT(DISTINCT f.name, f.value) = 2

See the demo.
Results:

> id | title  
> -: | :------
>  2 | song #2
user_104988280
Related