6

This code returns only posts having a rating. How I can get all posts with null value where the post does not have a rating?

select 
    posts.id, 
    posts.user_id, 
    posts.name, 
    posts.created_at,
    ratings.post_id,
    ratings.avg,
    ratings.count
from 
    posts
join (
    select
        post_id,
        avg(rating) as avg, 
        count(rating) as count
    from 
        ratings
    group by 
        ratings.post_id
) ratings
on ratings.post_id = posts.id

1 Answer 1

8

Have you tried using a LEFT OUTER JOIN? A LEFT OUTER JOIN will keep all the rows from the 'left' side of the join statement (posts table) that do not have a correponding row on the 'right' side of the statement (ratings table). The missing rows from the 'right' side will be filled with NULLS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.