SQL query – List albums where every song matches criteria

I am analyzing my music database which uses SQLite. I am trying to get a list of all albums where all the songs on that album having a rating of 10 or less.

Some contrived data for the example:

ALBUM       SONG            RATING
Coda        Poor Tom        10
Coda        Darlene         5
Coda        Hey Hey         20
Presence    For Your Life   10
Presence    Royal Orleans   5
Presence    Tea for One     10

Using this example data, I would want my query to return “Presence” since all the songs on that album have a rating of 10 or less.

I know enough to get the list of all album names, or the list of all songs with a rating of 10 or less, but I don’t know enough to combine them – let alone when EVERY song on an album fits my criteria. I’m sure there’s just some key word that I don’t know about so don’t know what to research.

Thanks.

Answer

Group by album and take only those groups having a max rating of 10 for every song

select album
from your_table
group by album
having max(rating) <= 10

Advertisement

Answer