I have been attempting to crack this nut for several hours now but I cannot figure out what combination of query code is required to achieve the exact results I am looking for.
I have two tables:
Music and Playlists
The fields for Music:
ID (Primary), Track_Name, File_Path, Date_Added, Artist, Album
The fields for Playlists:
PID, ID (many-to-one relation with Music), Track_Name, File_Path, Artist, Album, Number_of_Plays
What I am trying to do is for each ID in Music combine all of the records in all Playlists and then sum the Number_of_Plays field
Here is as far as I have gotten:
Select Music.ID, Music.Track_Name, Playlists.PID From Music Join Playlists On Music.ID = Playlists.ID Where Playlists.ID In ( Select ID From Playlists Group By ID Having Count(Number_of_Plays) > 1)
The only thing this produces is a list ordered by ID of records that have more than one number of plays. I have not figure out how to only show each song record once and then sum the number of plays for that song across all playlists.