你要left join
吗?从您当前的查询开始,它将是:
SELECT m.name, AVG(COALESCE(r.rating, 0)) AS avg_rating
FROM movies m
LEFT JOIN reviews r
ON m.id = r.movie_id
AND NOT EXISTS (
SELECT 1
FROM blocked b
WHERE b.blocked_cus_id = r.cus_id AND b.cus_id = 1
)
WHERE m.id = 2
GROUP BY m.id, m.name;