CODEWITHSUNDEEP
mysql
Aleksandr Fedotov
Aleksandr Fedotov

Reputation: 17

MySQL select Table with reviews (even with empty fields)

I need to calculate number of reviews and average grade from client_reviews table. And add these 2 fields to client_impression table. This MySQL reguest is working bu the problem that display fields only with reviews. But I need to display all fields even with no reviews. How to make it?

SELECT `impression`.*, review.count, review.grade FROM `client_impression` AS `impression` 
JOIN (SELECT impression_id, count(id) AS `count`, SUM(stars) AS `grade` 
    FROM `client_reviews` AS rev
      WHERE rev.status=1 AND (SELECT city_id FROM client_impression_offer WHERE id=rev.impression_offer_id) = 1
     GROUP BY rev.impression_id  ) AS review ON review.impression_id = `impression`.`id`
     
WHERE `impression`.`city_1`=1 AND `impression`.`id` IN (SELECT `impression` FROM `impression_to_rank` WHERE `rank`=6)

Upvotes: 1

Views: 23

Answers (1)

Fahmi
Fahmi

Reputation: 37473

Use left join

selct `impression`.*, review.count, review.grade FROM `client_impression` AS `impression` 
left join
(
SELECT impression_id, count(id) AS `count`, SUM(stars) AS `grade` 
FROM `client_reviews` AS rev WHERE rev.status=1 and
(SELECT city_id FROM client_impression_offer WHERE id=rev.impression_offer_id) = 1
group by impression_id
) review ON review.impression_id = `impression`.`id` and
`impression`.`city_1`=1 AND `impression`.`id` IN (SELECT `impression` FROM `impression_to_rank` WHERE `rank`=6)

Upvotes: 1

Related Questions