CODEWITHSUNDEEP
mysqlsql
andershagbard
andershagbard

Reputation: 1137

SQL Query selects the same data

In my query, rating_food, rating_service and rating_decor returns the same value, for all my results.

select `restaurants`.*, `rating`.*, (rating.rating_food + rating.rating_service + rating.rating_decor) / 3 as rating_total from
(
SELECT
avg(reviews.rating_food) * (- 1 / (0.33 * count(reviews.id) + 1) + 1) + 5 * 1 / (count(reviews.id) + 1 ) as rating_food,
avg(reviews.rating_service) * (- 1 / (0.33 * count(reviews.id) + 1) + 1) + 5 * 1 / (count(reviews.id) + 1 ) as rating_service,
avg(reviews.rating_decor) * (- 1 / (0.33 * count(reviews.id) + 1) + 1) + 5 * 1 / (count(reviews.id) + 1 ) as rating_decor
FROM restaurants
JOIN reviews ON reviews.restaurant_id = restaurants.id
)
as rating, restaurants
order by `rating_total` asc limit 12

Expected result:
A array of 12 results with three additional calculated columns (rating_food etc)

Right now i get 12 results but all their rating_food, rating_service and rating_decor returns the same value for all results.

Upvotes: 0

Views: 48

Answers (1)

eugenioy
eugenioy

Reputation: 12383

You need to join back your ratings subquery with the restaurants table.

Try this one:

select `restaurants`.*, `rating`.*, 
(rating.rating_food + rating.rating_service + rating.rating_decor) / 3 as rating_total from
(
SELECT
restaurants.id,
avg(reviews.rating_food) * (- 1 / (0.33 * count(reviews.id) + 1) + 1) + 5 * 1 / (count(reviews.id) + 1 ) as rating_food,
avg(reviews.rating_service) * (- 1 / (0.33 * count(reviews.id) + 1) + 1) + 5 * 1 / (count(reviews.id) + 1 ) as rating_service,
avg(reviews.rating_decor) * (- 1 / (0.33 * count(reviews.id) + 1) + 1) + 5 * 1 / (count(reviews.id) + 1 ) as rating_decor
FROM restaurants
JOIN reviews ON reviews.restaurant_id = restaurants.id
GROUP by restaurants.id
)
as rating join restaurants on rating.id = restaurants.id
order by `rating_total` asc limit 12

SQL Fiddle: http://sqlfiddle.com/#!9/50b39/4/0

Upvotes: 2

Related Questions