CODEWITHSUNDEEP
sqlselectgroup-byhaving
linkyndy
linkyndy

Reputation: 17900

SQL group by and keep only those groups that include given value of column

I have a songs table, a lyrics table and a tags table. A song may have multiple lyrics (or none), and each lyric has exactly one tag. So, in the lyrics table I have two foreign keys, song_id and tag_id, both linking to a record in the respective tables.

I would like to select all songs that don't have a given tag. For example, given the following view (the three tables are INNER JOINed):

song_id | lyric_id | tag_id | tag_value
---------------------------------------
1       | 1        | 1      | 'pop'
1       | 2        | 2      | 'hip-hop'
2       | 3        | 1      | 'pop'

given the 'hip-hop' tag, I need to retrieve the second song, since it has no linked lyric that has a linked tag with the 'hip-hop' value.

If I were to give the 'pop' tag, no songs would be retrieved, since both songs have a lyric that has the 'pop' tag linked.

How can I build such a query, in the most optimal way? I was thinking of grouping by song_id, then doing a HAVING(tag_value != 'hip-hop'), but unfortunately this isn't how HAVING works.

Upvotes: 1

Views: 59

Answers (1)

Mureinik
Mureinik

Reputation: 311188

This is a classic usecase for the [not] exists operator:

SELECT *
FROM   songs s
WHERE  NOT EXISTS (SELECT *
                   FROM   lyrics l
                   JOIN   tags t ON l.tag_id = t.id
                   WHERE  l.song_id = s.id AND 
                          t.tag_value = 'hip-hop') -- or any other tag

Upvotes: 3

Related Questions