For instance I have two tables in a music database.

Table album and table track.

Each track record has (amongst other things), an album_id and an artist_id

Each album record has a unique album_id

In order to spot which albums are by 'various artists' I want to count the number of distinct artist_id's which belong to each album.

Such that two or more track records with the same album_id but with differing artist_id's should signal that I can flag the album as 'various artists'

I can do it with multiple queries but I'm seeking the elegant all in one query.

  • So, you want a result with all the album_id that have more than 1 artist? All just count how many (such albums) exist? – ypercubeᵀᴹ Aug 8 '14 at 11:38
  • And what is your DBMS? SQL-Server, MySQL, Oracle, Postgres, ...? – ypercubeᵀᴹ Aug 8 '14 at 11:44
  • A result with all the album_id's – user45244 Aug 8 '14 at 11:53
  • Sorry, forget to answer DBMS question it's MySQL – user45244 Aug 8 '14 at 11:56

There are various ways to solve this problem, but with a composite index on (album_id, artist_id) most DBMS will manage to use only this index (a full index scan) with the following query and thus be quite efficient:

SELECT album_id
FROM track
GROUP BY album_id
HAVING MIN(artist_id) < MAX(artist_id) ;

If you also want the number of artists per album, you can add COUNT(DISTINCT artist_id) in the select list.

A less obfuscated alternative condition is to use (which could be less efficient than the above MIN < MAX method):

HAVING COUNT(DISTINCT artist_id) > 1 ;
| improve this answer | |
  • Thanks again for your help. This did the trick. I'd be interested to see other solutions out of curiosity. – user45244 Aug 8 '14 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.