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 beta@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/beta