How to query

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
www.blurredistinction.com
www.macromedia.com/support/forums/team_macromedia/

Re: How to query

Dave Mennenoh wrote:
> 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?

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
ORDER BY top_favorites DESCENDING

Regards,
Bill K.

Re: How to query

>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?

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