I have made a simple table in an open source database (LibreOffice Base) consisting of a record collection. It contains the artist, album, year, record label, and the record code as the primary key. I am trying to create an SQL query that will show ONLY the artists and the record labels when that artist was on multiple labels.
SELECT "RecordLabel", "ArtistName"
GROUP BY "ArtistName", "RecordLabel"
HAVING ( MIN ("RecordLabel") > 1) AND...
You can get the list of artists on multiple labels by using a query such as:
SELECT ArtistName FROM record_collection GROUP BY ArtistName HAVING COUNT(DISTINCT RecordLabel) > 1;
You can then use this in a query to get more details:
SELECT rc.* FROM record_collection rc WHERE rc.ArtistName IN (SELECT ArtistName FROM record_collection GROUP BY ArtistName HAVING COUNT(DISTINCT RecordLabel) > 1 );
A more clever approach chooses the records for an artist where there is another record for the artist with a different label:
SELECT rc.* FROM record_collection rc WHERE EXISTS (SELECT 1 FROM record_collection rc WHERE rc2.ArtistName = rc.ArtistName AND rc2.RecordLabel <> rc.RecordLabel );
This version probably has better performance with the right indexes.