Welcome to WebmasterWorld Guest from 54.196.144.100

Forum Moderators: open

Message Too Old, No Replies

Right join group by

Not getting the right results

     

Tommybs

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

5+ Year Member



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

Tommybs

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

5+ Year Member



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

jalicia18

9:38 am on Feb 4, 2011 (gmt 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

Tommybs

10:26 am on Feb 4, 2011 (gmt 0)

5+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month