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

sql mysql

1695 просмотра

2 ответа

279 Репутация автора

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?

Автор: Shuoling Liu Источник Размещён: 29.05.2009 09:40

Ответы (2)


1 плюс

349129 Репутация автора

SELECT  *
FROM    posts
WHERE   post_id NOT IN 
        (
        SELECT  comment_post
        FROM    comments
        WHERE   comment_date >= @deadline
        )
Автор: Quassnoi Размещён: 29.05.2009 09:43

4 плюса

35812 Репутация автора

Решение
SELECT *
FROM posts p
WHERE NOT EXISTS(
    SELECT 1
    FROM comments c
    WHERE c.comment_date >= 'deadline'
    AND p.post_id = c.post_id
)
Автор: Milan Babuškov Размещён: 29.05.2009 09:47
Вопросы из категории :
32x32