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

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.

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

You should remove user_ratings.rate from group by clause and add tbl_product.item_name.

SELECT
    company_details.v_id,
    company_details.company_name,
    tbl_product.item_name,
    COUNT(user_ratings.rate) as vote,
    avg(user_ratings.rate) as rate
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;

Query and result are both correct. Look at the rate

you can try this query it will return your exactly output according to your given 3 tables.

"select c.v_id , c.company_name , u.rate , p.item_name from company_details c  join  tbl_product p on p.item_id = c.item_id  join user_details u on c.v_id = u.v_id group by c.company_name ";