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

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.

Answer

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

Advertisement

Answer