Scott Killick Scott Killick - 7 months ago 16
SQL Question

SQL Query - How to show only where there are different values

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"
FROM "record_collection"
GROUP BY "ArtistName", "RecordLabel"
HAVING ( MIN ("RecordLabel") > 1) AND...


When record Labels are different for that same artist. IE, Frank Sinatra was on 3 different labels, for example, and I would want just his name and the 3 labels to show.

Any help would be greatly appreciated!

Answer

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.