SQL help!

Download and get help for different MediaMonkey Addons.

Moderators: Peke, Gurus

Pablo
Posts: 554
Joined: Sun Feb 22, 2004 2:59 am

SQL help!

Post by Pablo » Wed Mar 10, 2004 11:47 pm

I'm trying to restore the Play Counter (in case it has been changed) by looking at how many records are in the Played table with each given idSong. I'm able to use COUNT and GROUP BY to write a query of the ids next to how many times they were played. For instance,

Code: Select all

  SELECT Songs.ID, timesPlayed
  FROM Songs, [SELECT Played.idSong as SID, Count(Played.idSong) AS timesPlayed  FROM Played GROUP BY Played.idSong]. AS subq
  WHERE Songs.Id =subq.SID;
But I was unable to use this info to update the PlayCounter field in the Songs table.

Any ideas?

Pablo

Pablo
Posts: 554
Joined: Sun Feb 22, 2004 2:59 am

Post by Pablo » Thu Mar 11, 2004 7:36 am

I figured it out. JET SQL seems quite restrictive in the type of subqueries it allows, but I solved the problem by creating a temporary table to play the role of the subquery I needed.

Pablo

roylayer
Posts: 85
Joined: Tue Feb 25, 2003 12:44 am

Post by roylayer » Thu Mar 11, 2004 9:00 am

I was thinking it would be something like that. I am more familiar with Oracle SQL (I do Access queries through its GUI design mode.), so I couldn't really offer any suggestions. Glad you figured it out!
Happy user of MediaMonkey Gold version 2.5.5.998
Computer: p4, 2.5 ghz, 3 gb ram, win xp

Lowlander
Posts: 46172
Joined: Sat Sep 06, 2003 5:53 pm
Location: MediaMonkey 5

Post by Lowlander » Thu Mar 11, 2004 10:02 am

That is a good tip for Access programming. Create the queries in the Access interface (graphical) and show the code when you are done. This code you can use in your programs to access Access.
Lowlander (MediaMonkey user since 2003)

Post Reply