Select all records don't meet certain conditions in a joined table

Question

Let's say we have two tables here , posts and comments, the relations is one to many, there has a field call comment_date in the comments table.

Now I am struggle to get the expected results as follow:

All the posts have no comments after a certain date, can I done this by a SQL statement?

Answer 1

SELECT  *
FROM    posts
WHERE   post_id NOT IN 
        (
        SELECT  comment_post
        FROM    comments
        WHERE   comment_date >= @deadline
        )

Answer 2

select *
from Posts P
left join Comments C
on C.PostId = P.PostId
and C.Date > SomeDate
where C.PostId is null

This will get everything from Posts, matched to any comments greater than a given date (Everything from the Left table matched with any which meet the conditions from teh Right table), then restrict to only those Posts which don't have any comments in the where.

Answer 3

SELECT *
FROM posts p
WHERE NOT EXISTS(
    SELECT 1
    FROM comments c
    WHERE c.comment_date >= 'deadline'
    AND p.post_id = c.post_id
)