Philip Meyer;698559 Wrote:
> The thing is, you can have more than one album artist per album.
Just for testing, I have one album like this. There are two 'Ella &
Louis' albums in the library. For one of them I have the ALBUMARTIST
combined
ALBUMARTIST=Ella Fitzgerald & Louis Armstrong
ARTIST=Ella Fitzgerald
ARTIST=Louis Armstrong
and on the other I have two album artists
ALBUMARTIST=Ella Fitzgerald
ALBUMARTIST=Louis Armstrong
ARTIST=Ella Fitzgerald
ARTIST=Louis Armstrong
Here's what the query looks like for SQLite, sorted by
artist/year/album:
Code:
--------------------
-- Generate an album/artist list
SELECT ca.album AS album_id,
CAST(a.title AS CHAR) AS album,
a.year,
GROUP_CONCAT(ca.contributor, ", ") AS album_artist_id,
GROUP_CONCAT(CAST(c.name AS CHAR), ", ") AS album_artist,
GROUP_CONCAT(c.namesort, " ") AS album_artist_sort
FROM contributor_album ca
INNER JOIN albums a ON a.id = ca.album
INNER JOIN contributors c ON c.id = ca.contributor
WHERE ca.role = 5
GROUP BY ca.album
ORDER BY album_artist_sort, a.year, a.titlesort;
--------------------
Generating the following:
+-------------------------------------------------------------------+
|Filename: 2012-03-30_142026b.jpg |
|Download: http://forums.slimdevices.com/attachment.php?attachmentid=13205|
+-------------------------------------------------------------------+
--
JJZolx
------------------------------------------------------------------------
JJZolx's Profile: http://forums.slimdevices.com/member.php?userid=10
View this thread: http://forums.slimdevices.com/showthread.php?t=94357
_______________________________________________
beta mailing list
[email protected]
http://lists.slimdevices.com/mailman/listinfo/beta
