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.