problem with sort from voting table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

problem with sort from voting table

Author

Topic

juiceterry
Starting Member

4 Posts

Posted - 2008-10-22 : 17:03:56

Hello

We got an online radio with a voting/rating system for our tracks (1 - 5 stars). so there 3 tables: users, songs, user_song_votes

so now i want to display a detail page, which includes 2 lists:

- who rated this track positive ( > 4 stars)
- Which tracks were also rated positive by these users

well the first one was not that heavy, but the second one keeps me awake..

so thats what i've got (votes.user_id=1/2 are just sample data):
SELECT s.SongID, s.SongTitle, s.Cover, votes.user_id , votes.Vote, AVG(votes.Vote) AS average
FROM songs s INNER JOIN user_song_votes votes ON s.SongID=votes.songID
WHERE votes.user_id=1 OR votes.user_id=2
GROUP BY votes.songID HAVING average > 4 ORDER BY average DESC

first i though i'm getting the right data, just the sort is wrong, but when i print out "average" i get just wrong data.. anybody has a hint?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-22 : 18:31:38

hi, do you just want a list of Song Tracks that's also rated 5 by users who rated the previous song 5??
Why an average > 5? Can the user vote more than once for a song??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 00:20:11

try the below and see if you get average correctly


SELECT s.SongID, s.SongTitle, s.Cover, votes.user_id , votes.Vote, AVG(votes.Vote*1.0) AS average
FROM songs s INNER JOIN user_song_votes votes ON s.SongID=votes.songID
WHERE votes.user_id=1 OR votes.user_id=2 
GROUP BY votes.songID HAVING average > 4 ORDER BY average DESC

juiceterry
Starting Member

4 Posts

Posted - 2008-10-23 : 02:26:04

Thanx for the responses..

@visakh16: i will try the adjustement in average calculation..

@banbingl: thats exactly what i am trying to do.. i have a list of users, who rated THIS Track/Compilation with e.g. 4 stars.. no i want to list the tracks/compilations, which where also rated with 4 stars or higher by these users..

to get an idea: http://www.electroradio.ch/test?songid=44 .. Ministry of Sound Compilation is the Actual Track/Compilation..
Benutzer Ratings: These Users gave the Ministry of sound Compilation 4 stars of more
Andere Tracks/Compilations: There should be the list with the tracks/compilations, which were also rated with 4 or more stars by these users.

A user can only rate ONCE for the same track/compilation..

sorry, i am relatively new to SQL..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 02:54:54

[code]SELECT s.SongID,
s.SongTitle,
s.Cover,
v.user_id ,
v.Vote,
votes.AvgRating,
LEFT(tl.trklst,LEN(tl.trklst)-1) AS otherpositivetracks
FROM songs s
INNER JOIN user_song_votes v
ON v.SongID=s.SongID
OUTER APPLY (SELECT AVG(Vote*1.0) AS AvgRating
FROM user_song_votes
WHERE SongID=s.SongID
)votes
OUTER APPLY (SELECT s1.SongTitle + ','
FROM user_song_votes v1
INNER JOIN Songs s1
ON s1.SongID=v1.SongID
WHERE s1.SongID<>s.SongID
AND v1.user_id=v.user_id
AND vote>4
FOR XML PATH)tl(trklst)
HAVING votes.AvgRating) > 4
ORDER BY votes.AvgRating DESC,SongID [/code]

juiceterry
Starting Member

4 Posts

Posted - 2008-10-23 : 03:29:47

thanx very much..
i will try that..

what i have to say is that the application runs on mysql.. i thought it will be a standard sql code..
i hope OUTER APPLY will work for my case..
what i do not understand ist the LEFT(tl.trklst,LEN(tl.trklst)-1) AS otherpositivetracks part..
what is tl.trklst?
where is the list of user ids from which i want other positive voted tracks?

juiceterry
Starting Member

4 Posts

Posted - 2008-10-24 : 03:15:11

right, i think this will do the trick (2 statements):

$sql = "SELECT votes.user_id, votes.Vote, votes.songID
FROM user_song_votes votes
WHERE (" . $sql_userids . ") AND votes.Vote > 4 GROUP BY votes.songID";

$sql = "SELECT s.SongID, s.SongTitle, s.Cover, votes.Vote, votes.songID, AVG(votes.Vote) AS average
FROM songs s INNER JOIN user_song_votes votes ON s.SongID=votes.songID
WHERE (" . $songsSQL . ") GROUP BY votes.songID HAVING average > 4.5 ORDER BY average DESC";

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 03:54:17

quote:
Originally posted by juiceterry

thanx very much..
i will try that..

what i have to say is that the application runs on mysql.. i thought it will be a standard sql code..
i hope OUTER APPLY will work for my case..
what i do not understand ist the LEFT(tl.trklst,LEN(tl.trklst)-1) AS otherpositivetracks part..
what is tl.trklst?
where is the list of user ids from which i want other positive voted tracks?

you must have specified this in beginning. this is MS SQL Server forum.All syntaxes in MS SQL Server wont work fine with MySQL.So you may be better posting this in MySQL forums.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources