选择不在查询中的数据
问题描述:
我想选择所有未被用户标记为已读的帖子。选择不在查询中的数据
tbl_post
post_id post_message
1 hello world
2 good night
3 good morning
4 incredible
5 cool
tbl_mark_as_read
user_id post_id
3 1
3 4
我要选择不会在此查询
SELECT p.post_id,p.post_message FROM tbl_post AS p
LEFT JOIN tbl_mark_as_read AS r
ON r.post_id = p.post_id
AND r.user_id = 3
我想选择的所有数据输出到像
post_id post_message
2 good night
3 good morning
5 cool
答
您可以使用not in
操作:
SELECT *
FROM tbl_post
WHERE post_in NOT IN (SELECT post_id FROM tbl_mark_as_read);
如果你想给该用户3已经阅读仅仅排除的职位,你可以添加一个where
子句内查询:
SELECT *
FROM tbl_post
WHERE post_in NOT IN (SELECT post_id
FROM tbl_mark_as_read
WHERE user_id = 3);
答
试试这个:
SELECT p.post_id,p.post_message FROM tbl_post AS p
LEFT JOIN tbl_mark_as_read AS r
ON r.post_id = p.post_id
AND r.user_id = 3
where r is null
这就是我正在寻找的查询,谢谢你兄弟。 – 2014-10-08 13:28:04