Search code examples
sqlruby-on-railsjoinfind-by-sql

sql left join where rails

Because I use rails I've become rusty on sql since I rarely use it in rails. I have two related tables: comments 1:m comment_views

I want to find all comments where comment_views.viewed is false. Problem is, for some comments there is not a relating record in comment_views yet.

So far I have

select comments.id 
    from comments 
        left join comment_views 
            on comments.id = comment_views.comment_id 
    where comment_views.viewed != "t" 
    group by type_id, object_id 
    order by comments.created_at desc

But as stated, that doesn't return comments when there is no record in comment_views.

Solution

  • You can check for null if there is no record...

    where comment_views.viewed != "t" or comments_views.viewed is null