How to query

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I am potentially building a music listening site and want the users to be
able to place songs into their favorites list. Easy enough, stick the id of
the song, from the songs table, into the users favorites. From this however,
how would I go about selecting the songs that are most in peoples
favorites - ie top favorites?

Dave -
Adobe Community Expert

Re: How to query

Dave Mennenoh wrote:
Quoted text here. Click to load it

Well, you probably need a third table, referencing both songs and users.
This is called a many-to-many table or a join table.

SELECT s.song_id, s.song_name, COUNT(*) AS top_favorites
FROM songs AS s INNER JOIN user_songs AS u USING (song_id)
GROUP BY s.song_id

Bill K.

Re: How to query

Quoted text here. Click to load it

Assuming you've got a table 'favorites' where the userid and songid
are listed, one row per (userid, songid) pair, you can get the top list with:

    select songid, count(songid) as cnt from favorites
    group by songid order by cnt desc limit 10;

Join with the songs table to get the actual title, if desired.

                        Gordon L. Burditt

Site Timeline