homepage Welcome to WebmasterWorld Guest from 54.166.39.179
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Right join group by
Not getting the right results
Tommybs

5+ Year Member



 
Msg#: 4260752 posted 7:55 pm on Jan 31, 2011 (gmt 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

 

Tommybs

5+ Year Member



 
Msg#: 4260752 posted 5:03 pm on Feb 1, 2011 (gmt 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

jalicia18



 
Msg#: 4260752 posted 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

5+ Year Member



 
Msg#: 4260752 posted 10:26 am on Feb 4, 2011 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved