Sometime a little nudge is what we need :) I had been meaning to install and get familiar with SQLite Expert but have found any number of reasons not to. But when I decided to try LMS Extractor it was immediately obvious that I would need something to reveal the table structures if I wanted to do much beyond your default queries.
So I now am using both, SQLite Expert as a query building tool and LMS Extractor as an easy way to save the results in a useful format. I have long wanted a way to build a catalog of my albums and in addition to the artist, title, genre and date, identify which format (codec and bitrate) they are in. The following SQL query works pretty well. It's not necessarily 100% accurate as it only uses data from the first track of the album, but it's close enough for my needs. Note the need for single quotes around Compilation. That threw me for a few minutes when "1" kept showing up as the Artist for compilation albums. SELECT CASE WHEN al.compilation = 1 THEN CAST('Compilation' AS CHAR(150)) ELSE TRIM(CAST(co.name AS CHAR(150))) END AS "Artist", TRIM(CAST(al.title AS CHAR(150))) AS "Album Title", TRIM(CAST(gn.name AS CHAR(150))) AS "Genre" , al.year AS "Year", CASE WHEN tr.content_type = "flc" THEN "FLAC" WHEN tr.content_type = "mp3" THEN "MP3" WHEN tr.content_type = "mp4" THEN "MP4" WHEN tr.content_type = "wma" THEN "WMA" ELSE tr.content_type END AS "Codec", ROUND((tr.bitrate/1000),0) AS "Bitrate (Kbps)", CASE WHEN tr.vbr_scale = 1 THEN "VBR" ELSE "CBR" END AS "VBR/CBR", CASE WHEN tr.lossless = 1 THEN "YES" ELSE "NO" END AS "Lossless" FROM albums al, tracks tr, contributor_track ct, contributors co, genre_track gt, genres gn WHERE al.id = tr.album AND tr.tracknum = "1" AND ct.track = tr.id AND ct.role IN (1, 5) AND co.id = ct.contributor AND gt.track = tr.id AND gn.id = gt.genre ORDER BY 1, 2 Win7Pro(x64)[3.3Ghz i5, 8GB RAM, 120GB SSD system, 15TB storage], LMS 7.7.3 -> Logitech Squeezebox Classic V.3 -> Cambridge Audio DacMagic -> NAD C160 -> 2 x NAD C272 -> Quad 22L2 ------------------------------------------------------------------------ get.amped's Profile: http://forums.slimdevices.com/member.php?userid=10022 View this thread: http://forums.slimdevices.com/showthread.php?t=101600 _______________________________________________ discuss mailing list discuss@lists.slimdevices.com http://lists.slimdevices.com/mailman/listinfo/discuss