CODEWITHSUNDEEP
mysqljoingroup-by
Amar Banerjee
Amar Banerjee

Reputation: 5012

Not getting proper result when using group by with join in mysql

I have 3 tables named users, restaurants, reviews. Here id (primary key) of users is act as foreign key uid in restaurants and in reviews table rate_to is associate with uid of restaurants and rate_by is associate with id of users table. I want all restaurants belongs to that users from restaurants table but sorted by their avg_rate from reviews table. I have used this query for this.

 SELECT `Restaurant`.`id`, `Review`.`avg_rating`, `Review`.`rate_to`
 FROM `users` AS `User`
 LEFT JOIN `reviews` AS `Review` ON (`User`.`id` = `Review`.`rate_to`)
 LEFT JOIN `restaurants` AS `Restaurant` ON (`User`.`id` = `Restaurant`.`uid`) 
 WHERE  `User`.`type` = '0'  AND  `User`.`isdeleted` = '0'
 GROUP BY `Review`.`rate_to` ORDER BY `Review`.`avg_rating` DESC

But when I am using this it gives me only those restaurants which have entry in reviews table, but I want all distinct restaurants from restaurants table with sorted by avg_rate in reviews tables.

Please help.

Upvotes: 1

Views: 45

Answers (4)

alf
alf

Reputation: 8513

Try listing those in the opposite order, e.g.

 SELECT `Restaurant`.`id`, `Review`.`avg_rating`, `Review`.`rate_to`
 FROM `restaurants` AS `Restaurant` 
 LEFT JOIN `reviews` AS `Review` ON (`Review`.`rate_to` = `Restaurant`.`uid`) 
 LEFT JOIN `users` AS `User` ON (`User`.`id` = `Review`.`rate_by`)
 WHERE  `User`.`type` = '0'  AND  `User`.`isdeleted` = '0'
 GROUP BY `Restaurant`.`id` ORDER BY `Review`.`avg_rating` DESC

That'll make sure you get all the restaurants. Still, it won't work, either, as Review.avg_rating and Review.rate_to are not aggregate functions — and if you need an aggregate, I cannot see how User gets into play here.

Try 

 SELECT `Restaurant`.`id`, AVG(`Review`.`avg_rating`)
 FROM `restaurants` AS `Restaurant` 
 LEFT JOIN `reviews` AS `Review` ON (`Review`.`rate_to` = `Restaurant`.`uid`) 
 GROUP BY `Restaurant`.`id` 
 ORDER BY AVG(`Review`.`avg_rating`) DESC

That should be better, but you'll include all the deleted users.

In order to get rid of the reviews left by the deleted users, you'll probably need a subquery:

 SELECT `Restaurant`.`id`, AVG(`Review`.`avg_rating`)
 FROM `restaurants` AS `Restaurant` 
 LEFT JOIN (select r.* from `reviews` r
     JOIN `users` AS `User` ON (`User`.`id` = `Review`.`rate_by`)
     WHERE  `User`.`type` = '0'  AND  `User`.`isdeleted` = '0'
 ) AS `Review` ON (`Review`.`rate_to` = `Restaurant`.`uid`) 
 GROUP BY `Restaurant`.`id` 
 ORDER BY AVG(`Review`.`avg_rating`) DESC

Since you have not provided us with DDL, I have not tested any of those.

Upvotes: 0

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

You have (non-aggregated) fields in you SELECT list that are not mentioned in your GROUP BY.

Most DBMSes forbid that and for a good reason. MySQL does not, and as a consequence you'll get what are essentially random results.

Upvotes: 1

Moumita
Moumita

Reputation: 360

I can see here is no use of user table for your result.You can try this:

SELECT `Resturant`.`id` , MAX( `Review`.`avg_rating` ) AS max_avg, `Review`.`rate_to` FROM `resturants` AS `Resturant` LEFT JOIN reviews AS Review ON ( `Resturant`.`uid` = `Review`.`rate_to` ) WHERE 1 GROUP BY `Resturant`.`id` ORDER BY `Review`.`avg_rating` DESC

Upvotes: 1

user2374840
user2374840

Reputation:

I am not sure if the left join works two times in this case. Maybe you should use subquery?

Upvotes: 0

Related Questions