如何使用3个表的连接计算列数据

Question

I have three tables

tbl_product 
item_id | item_name

company_details
v_id | item_id | company_name

user_ratings 
r_id | rate | v_id 

I want to count the rate and also get the rate of the company. Here is my query

SELECT  company_details.v_id,
        company_details.company_name,
        COUNT(user_ratings.rate) as vote,
        user_ratings.rate,
        tbl_product.item_name
FROM    company_details
LEFT JOIN tbl_product ON tbl_product.item_id = company_details.item_id
LEFT JOIN user_ratings ON user_ratings.v_id = company_details.v_id
GROUP BY company_details.v_id, user_ratings.rate

This is Whts i am Getting after this query:

v_id  company_name           vote  rate  item_name  
1     The Oberoi Udaivilas   1     4     5 Star Hotels  
1     The Oberoi Udaivilas   1     5     5 Star Hotels  
2     The Taj Mahal Palace   2     5     4 Star Hotels  
3     Rambagh Palace         1     5     3 Star Hotels  
4     Taj Lake Palace        1     5     5 Star Hotels  
5     Windflower Hall        1     3     2 Star Hotels  
5     Windflower Hall        1     5     2 Star Hotels  
6     Leela Palace Kempinski 0     n     4 Star Hotels  
7     Umaid Bhawan Palace    0     n     4 Star Hotels  
8     Hotel Ratan Vilas      0     n     4 Star Hotels  
9     The Leela Palace       0     n     4 Star Hotels  
10    The Imperial Hotel     0     n     3 Star Hotels  

You can see vote column is not counting.

This is what I am expecting

v_id  company_name          vote  rate  item_name   
1     The Oberoi Udaivilas  2     5     5 Star Hotels   
2     The Taj Mahal Palace  2     5     4 Star Hotels   

But this query is not counting the rate from user_ratings table, because of I also want to get the rate, if I remove the user_ratings.rate from select clause, then this query works, but when I add the user_ratings.rate in the select clause, then this query is not counting the rates as(vote), and it is returning as one rows in every count.

Answer 1

Seems to me that your query is only missing an aggregation on the rate column, and from the expected output I'd say it's a max. You also should fix your group by. Try this one

SELECT  company_details.v_id,
        company_details.company_name,
        COUNT(user_ratings.rate) as vote,
        MAX(user_ratings.rate) as rate,
        tbl_product.item_name
FROM    company_details
LEFT JOIN tbl_product ON tbl_product.item_id = company_details.item_id
LEFT JOIN user_ratings ON user_ratings.v_id = company_details.v_id
GROUP BY company_details.v_id,
         company_details.company_name,
         tbl_product.item_name