Welcome to WebmasterWorld Guest from 54.163.49.19

Forum Moderators: open

Message Too Old, No Replies

Right join group by

Not getting the right results

     
7:55 pm on Jan 31, 2011 (gmt 0)

Full Member

5+ Year Member

joined:June 21, 2007
posts: 316
votes: 0


Posted this is the php forum but it makes more sense in here!

I have the following 2 tables

+--------+--------+-----+
| pic_id | rating | uid |
+--------+--------+-----+
| 1 | 4 | 1 |
| 2 | 3 | 1 |
| 3 | 6 | 2 |
| 4 | 2 | 1 |
| 5 | 1 | 2 |
+--------+--------+-----+

+------------+--------+-----+
| comment_id | rating | uid |
+------------+--------+-----+
| 1 | 1 | 2 |
| 2 | 3 | 1 |
| 3 | 4 | 3 |
| 4 | 3 | 2 |
+------------+--------+-----+

What I want to do is get an overall score for a user based on all their ratings (so assume comment_id 1 belongs to uid 2 as well as pic_id 3) Both 'entities' belong to the user and the rating is a sum already calculated by a trigger. i.e. it's an overall rating for that pic/comment based on other user ratings.

What I want to do is get an overall score for the user based on how people have rated all their pics and comments. There is the possibilty that the user might have left comments but not have any pictures hence the right join.
I've tried like so :


select
sum(p.rating) as prating,
sum(c.rating) as crating ,
(sum(c.rating) + sum(p.rating)) as orating ,
p.uid as puid,
c.uid as cuid
from pic_rating as p
right join comment_rating as c
on p.uid = c.uid
group by p.uid, c.uid
order by orating desc


But this produces the following, incorrect results:

prating 14 crating 8 uid is 2 and orating is 22
--expected prating 7, crating 4, orating 11
prating 9 crating 9 uid is 1 and orating is 18
--expected prating 9, crating 3 orating 12
prating crating 4 uid is 3 and orating is 4
--expected prating 0, crating 4, orating 4

Can anybody see what I am doing wrong here? Or suggest an alternative way to do this? I don't really want to run a second query if I can avoid it, but if that's my only option and then performing the addition in php I guess that's what I'll have to do

Cheers
5:03 pm on Feb 1, 2011 (gmt 0)

Full Member

5+ Year Member

joined:June 21, 2007
posts: 316
votes: 0


Well I worked out how to do this using the following query:

SELECT p.prating,c.crating, p.uid as puid, c.uid as cuid, (p.prating + c.crating) as orating FROM (SELECT sum(rating) as prating, uid from pic_rating group by uid)as p right join (SELECT sum(rating) as crating,uid FROM comment_rating group by uid) as c
on p.uid = c.uid

Then all I need is quick check in the code to just use one of the ids i.e if p.uid is null but there is a c.uid row
9:38 am on Feb 4, 2011 (gmt 0)

New User

5+ Year Member

joined:Oct 20, 2010
posts:5
votes: 0


is this what you want?


SELECT a.pic_id, a.rating, a.uid, b.comment_id, b.rating, b.uid FROM table1 a, table2 b WHERE a.uid=b.uid GROUP BY a.uid ORDER BY a.pic_id
10:26 am on Feb 4, 2011 (gmt 0)

Full Member

5+ Year Member

joined:June 21, 2007
posts: 316
votes: 0


No I tried solutions like that, In fact in the above code I also need a union clause after running the exact same query but using a left join as well. This way it returns all entries from both tables and correctly scores them. Thanks for your input though.