SQL / scripting help - search by Artist ID
Posted: Sun Feb 10, 2019 11:07 am
I'm trying to write a script that will either look up genre from Musicbrainz or find the most common genre from the same artist in the existing database. A few artists have special characters (') so the script errors out when searching by itm.ArtistName, otherwise this query works:
"SELECT Genre, Count(Genre) AS SortField FROM Songs WHERE Artist = '" & itm.ArtistName & "' AND Genre <> '' GROUP BY Genre ORDER BY SortField DESC"
I'm trying to search by Artist ID instead to avoid the above error but getting nowhere with this - I just don't know SQL that well and I'm not sure of the syntax. Here's where I am right now - any thoughts on why this isn't working correctly?
"SELECT Songs.Genre, Count(Songs.Genre) AS SortField FROM Songs, ArtistsSongs WHERE Songs.ID = ArtistsSongs.IDSong AND ArtistsSongs.IDArtist = '" & itm.Artist.ID & "' AND Genre <> '' GROUP BY Genre ORDER BY SortField DESC"
"SELECT Genre, Count(Genre) AS SortField FROM Songs WHERE Artist = '" & itm.ArtistName & "' AND Genre <> '' GROUP BY Genre ORDER BY SortField DESC"
I'm trying to search by Artist ID instead to avoid the above error but getting nowhere with this - I just don't know SQL that well and I'm not sure of the syntax. Here's where I am right now - any thoughts on why this isn't working correctly?
"SELECT Songs.Genre, Count(Songs.Genre) AS SortField FROM Songs, ArtistsSongs WHERE Songs.ID = ArtistsSongs.IDSong AND ArtistsSongs.IDArtist = '" & itm.Artist.ID & "' AND Genre <> '' GROUP BY Genre ORDER BY SortField DESC"