Philip Meyer;435436 Wrote:
> What is the easiest way of extracting the list of artists from
> Squeezebox Server? I tried to get the list of artists from the CLI
> using "artists" command, but the output isn't very friendly.
>
> I can extract the distinct list of album artists from the albums table
> from each DB engine, but this isn't the same SQL that is used to
> populate the Browse Artists list.
If you turn on SQL debugging you can easily find the SQL statement used
to get the count for the library stats.
Code:
--------------------
SELECT COUNT( * )
FROM ( SELECT me.id
FROM contributors me
LEFT JOIN contributor_album contributorAlbums ON
contributorAlbums.contributor = me.id
LEFT JOIN albums album ON album.id = contributorAlbums.album
WHERE ( ( ( album.compilation IS NULL OR album.compilation = ? )
AND contributorAlbums.role IN ( ?, ? ) ) )
GROUP BY me.id ) count_subq: '0', '1', '5'
--------------------
So, to retrieve the contributor list, use the subquery.
Code:
--------------------
SELECT c.name
FROM contributors c
LEFT JOIN contributor_album ca ON ca.contributor = c.id
LEFT JOIN albums a ON a.id = ca.album
WHERE (((a.compilation IS NULL OR a.compilation = 0) AND ca.role IN (1,5)))
GROUP BY c.id
ORDER BY c.name;
--------------------
--
JJZolx
Jim
------------------------------------------------------------------------
JJZolx's Profile: http://forums.slimdevices.com/member.php?userid=10
View this thread: http://forums.slimdevices.com/showthread.php?t=64890
_______________________________________________
beta mailing list
[email protected]
http://lists.slimdevices.com/mailman/listinfo/beta
