foo_sqlite - Page 2

Topic: foo_sqlite (Read 4780 times) previous topic - next topic

0 Members and 1 Guest are viewing this topic.

Re: foo_sqlite

Reply #25 – 2019-03-16 11:54:33

Quote from: kugel on 2019-03-16 10:59:53

It seems to work.

Well, at least the statement, which you posted here, won't be executed as the syntax is wrong. Anyway, try this:

Code: [Select]

WITH Albums AS (
  SELECT album
  FROM MediaLibrary
  WHERE path LIKE 'D:\Metal\%' AND substr(path, 10, 1) > 'M'
  GROUP BY album
  HAVING avg(rating)>3 OR min(rating)=''
)
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, 'SQL_>M'
FROM MediaLibrary a JOIN Albums ON a.album = Albums.album

Re: foo_sqlite

Reply #26 – 2019-03-16 13:47:45

Oh sorry I forgot to remove the extra columns I used for debugging without "INSTER INTO..."-line. I used the columns to directly verify whether the criteria where applied.

Your code looks much more clean, but it does also pull in tracks from other folders. There are more tracks in the list than there are in the Metal-folder. With your code I can also not see the results in the console if I remove the "INSTER INTO..."-line. I do not understand how the tracks from other folders make it into the list...

Re: foo_sqlite

Reply #27 – 2019-03-16 14:58:16

Quote from: kugel on 2019-03-16 13:47:45

but it does also pull in tracks from other folders.

This is only possible, if you have have albums from the metal folder with the same name in other folders. In this case you could try this (assuming that all selected album names are distinct):

Code: [Select]

WITH Albums AS (
  SELECT album, max(substr(path,1,10)) path
  FROM MediaLibrary
  WHERE path LIKE 'D:\Metal\%' AND substr(path, 10, 1) > 'M'
  GROUP BY album
  HAVING avg(rating)>3 OR min(rating)=''
)
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, 'SQL_>M'
FROM MediaLibrary a JOIN Albums ON a.album = Albums.album AND a.path LIKE Albums.path||'%'

Re: foo_sqlite

Reply #28 – 2019-03-16 15:49:13

Quote from: fbuser on 2019-03-16 14:58:16

Quote from: kugel on 2019-03-16 13:47:45
but it does also pull in tracks from other folders.
This is only possible, if you have have albums from the metal folder with the same name in other folders. In this case you could try this (assuming that all selected album names are distinct):

Code: [Select]
WITH Albums AS (
  SELECT album, max(substr(path,1,10)) path
  FROM MediaLibrary
  WHERE path LIKE 'D:\Metal\%' AND substr(path, 10, 1) > 'M'
  GROUP BY album
  HAVING avg(rating)>3 OR min(rating)=''
)
INSERT INTO Playlist_Updatable(metadb_handle,playlist_name)
SELECT a.metadb_handle, 'SQL_>M'
FROM MediaLibrary a JOIN Albums ON a.album = Albums.album AND a.path LIKE Albums.path||'%'

Ah that's it! Thank you for your help. I am happy with your latest query suggestion