John Gate John Gate - 6 months ago 8
SQL Question

Multiple values in same cell

I'm trying to make a description of a track including all details related to it.

SELECT
(SELECT
UPPER(Track.Name) || ' is a ' ||
CAST(Track.Milliseconds/1000 AS INT) || ' seconds long track in the album ' ||
UPPER(Album.Title) || ' of ' ||
Artist.Name || ' composed by ' ||
CASE WHEN Track.Composer IS NULL THEN 'an unknown composer' ELSE Track.Composer END ||
'. ' || 'It is available as a ' ||
MediaType.Name || ' for $' ||
Track.UnitPrice || ', and it can be found in the following playlists: ' ||
Playlist.Name )
AS 'Track Description' FROM Track
LEFT JOIN Album
ON Track.AlbumId=Album.AlbumId
INNER JOIN Artist
ON Artist.ArtistId=Album.ArtistId
INNER JOIN MediaType
ON Track.MediaTypeId=MediaType.MediaTypeId
INNER JOIN PlaylistTrack
ON PlaylistTrack.TrackId = Track.TrackId
INNER JOIN Playlist
ON Playlist.PlaylistId = PlaylistTrack.PlaylistId
ORDER BY RANDOM()
LIMIT 1;


I'm up to working with
playlist.name
. One track can be in more than one playlist and currently it only outputs the top playlist. I want to know how i can output all playlists the track is in separated by commas after
and it can be found in the following playlists: ' ||

Answer

One strategy would be to use GROUP_CONCAT to aggregate all the playlist names in which a given track appears. To do this, you can GROUP BY every column associated with a track except for the playlist name. The subquery computes your output in pretty format from the inner query.

SELECT UPPER(t.trackName) || ' is a ' || 
    CAST(t.trackMillis/1000 AS INT) || ' seconds long track in the album ' ||
    UPPER(t.albumTitle) || ' of ' || 
    t.artistName || ' composed by ' || 
    CASE WHEN t.trackComposer IS NULL THEN 'an unknown composer' ELSE t.trackComposer END ||
    '. ' || 'It is available as a ' || 
    t.mediaTypeName || ' for $' || 
    t.trackUnitPrice || ', and it can be found in the following playlists: ' || t.playlistNames
FROM
(
    SELECT Track.Name AS trackName, Track.Milliseconds AS trackMillis,
        Album.Title AS albumTitle, Artist.Name AS artistName,
        Track.Composer AS trackComposer, MediaType.Name AS mediaTypeName,
        Track.UnitPrice AS trackUnitPrice, GROUP_CONCAT(Playlist.Name) AS playlistNames
    FROM Track
    LEFT JOIN Album
        ON Track.AlbumId = Album.AlbumId
    INNER JOIN Artist
        ON Artist.ArtistId = Album.ArtistId
    INNER JOIN MediaType
        ON Track.MediaTypeId = MediaType.MediaTypeId
    INNER JOIN PlaylistTrack
        ON PlaylistTrack.TrackId = Track.TrackId
    INNER JOIN Playlist
        ON Playlist.PlaylistId = PlaylistTrack.PlaylistId
    GROUP BY Track.Name, Track.Milliseconds, Album.Title, Artist.Name,
        Track.Composer, MediaType.Name, Track.UnitPrice
) t
Comments