Simple JOIN question.

[LucL]

Hi Guys,

My query joins 2 different tables and the third original table.

I'm getting the COUNT from one of the joined tables and a SUM from the other.

Query:

"SELECT u_id,u_handle,COALESCE(SUM(ratings.r_rating),0) as u_rating,FORMAT(COUNT(d_id),0) as u_domains FROM users LEFT JOIN ratings on ratings.r_rated=users.u_handle LEFT JOIN domains ON domains.d_user=users.u_handle WHERE u_handle='Test' GROUP by r_id LIMIT 1"

The only problem is that the second JOIN COUNT is 232 (domains for the specified user) and that affects the SUM RATING, because the sum is computed for each one of the results from the second JOIN (domains count).

I know the problem is with my joins or with my group, but I can't figure out which.

Thanks Guys!
Luc L.

 

[r937]

select u_id
     , u_handle
     , ( select sum(r_rating)
           from ratings 
          where r_rated = users.u_handle )
                 as u_rating
     , ( select count(d_id)
           from domains
          where d_user = users.u_handle )
                 as u_domains 
  from users 
 where u_handle = 'Test'

r937.com | rudy.ca

 

[LucL]

that's amazing. i didn't know you could do multiple queries like that. for some reason i thought mysql didn't allow that.

thanks again r937!

 

[r937]

well, it didn't, before version 4.1



r937.com | rudy.ca

 

[LucL]

r937, recommend any good books to learn mysql inside out?

 

[r937]

mysql.com has all you need



r937.com | rudy.ca

 

[LucL]

thanks!