How do I count the number of different child id's belonging to a parent record? 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.
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) &lt; 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 &lt; MAX method):

HAVING COUNT(DISTINCT artist_id) &gt; 1 ;