SQL question

[Eraser]

I have two tables. One table "songs" with a list of titles from songs. Each song also has a unique ID number.
In another table, called "favorites", I store which user has which song listed as favorite. So this table contains records which two attributes, "userid", which references to users (not relevant for this question) and "songid", which references to a song.

Now, what I want to do is, figure out which are the most popular songs (the songs that have the most records in the favorites table). I could simply do

SELECT count(songtitle) AS favCount, songid
FROM favorites
GROUP BY songid
ORDER BY count(game) DESC;

Which gives me the most popular songid's. Now, obviously, I want to know not the songids, but the songtitles (from table songs). I've tried various things with left joins but nothing seems to work. What makes it even worse is that I'm stuck to MS Access.

Can anyone help me write a query that does what I want?

[Denz]

Pho will know this one.

[Fender]

select S.name
from songs S, favorites F
where S.song_id = F.song_id
and F.user_id = ?

[4days]

you might need a 'SELECT DISTINCT(songtitle)' in there.

[neh]

select count(*), songtile
from songs s, favs f
where s.id = f.songid
group by songtitle

[^misantropia^]

Shouldn't this do the trick?

Code: Select all

SELECT COUNT(*) AS count, s.title
FROM favorites f, songs s
WHERE s.songid = f.songid
GROUP BY f.songid
ORDER BY count DESC

[^misantropia^]

And just for kicks, the LEFT JOIN version (I didn't use the original field names though):

Code: Select all

SELECT COUNT(*) AS count, s.title
FROM favorites f
LEFT JOIN songs s ON s.songid = f.songid
GROUP BY f.songid
ORDER BY count DESC

[bitWISE]

To have the query return the actual most popular song you need to use one of these.

Code: Select all

SELECT routename
FROM troute
where routeid = (SELECT top 1 assignedto
	FROM tevent
	GROUP BY assignedto
	order by count(assignedto) desc)


SELECT top 1 routename
FROM tevent
inner join troute on troute.routeid=tevent.assignedto
GROUP BY routename
order by count(assignedto) desc

According to Query Analyzer, the first query is 1% faster in our local database and 4% faster in the production database. So, the second way may look cleaner but outer/inner joins on large tables are extremely costly.

[Eraser]

Shouldn't this do the trick?

Code: Select all

SELECT COUNT(*) AS count, s.title
FROM favorites f, songs s
WHERE s.songid = f.songid
GROUP BY f.songid
ORDER BY count DESC

I thought that should do it as well. It gives me the error "you tried to execute a query that does not include the specified expression 'songtitle' as part of an aggregate function."

[bitWISE]

Shouldn't this do the trick?

Code: Select all

SELECT COUNT(*) AS count, s.title
FROM favorites f, songs s
WHERE s.songid = f.songid
GROUP BY f.songid
ORDER BY count DESC

I thought that should do it as well. It gives me the error "you tried to execute a query that does not include the specified expression 'songtitle' as part of an aggregate function."

If you want that version just replace the group by f.songid with s.title.