我正在尝试使用id从另一个表的联接中检查特定记录,但是另一个表具有多个具有相同id的记录,我想选择该列中没有所有人的记录。
我的SQL是:
SELECT b.*
, a.*
FROM engine4_blog_blogs b
LEFT
JOIN engine4_authorization_allow a
ON a.resource_id = b.blog_id
WHERE a.resource_type = 'blog'
AND a.role NOT IN ('everyone')
AND a.action = 'view'
AND b.draft = 0
AND b.search = 1
ORDER
BY b.creation_date DESC
我只想选择没有所有人的记录,并且只选择一行而不重复。
blog 2 view everyone 0 1 NULL
blog 2 view owner_member 0 1 NULL
blog 2 view owner_member_member 0 1 NULL
blog 2 view owner_network 0 1 NULL
blog 2 view registered 0 1 NULL
谢谢,
最佳答案
如果要选择没有“所有人”的行,则需要将第二个表上的所有条件移至on
子句:
SELECT b.*, a.*
FROM engine4_blog_blogs b LEFT JOIN
engine4_authorization_allow a
ON a.resource_id = b.blog_id AND
a.resource_type = 'blog' AND
a.role IN ('everyone') AND
a.action = 'view'
WHERE b.draft = 0 AND b.search = 1 AND a.resource_id IS NULL
ORDER BY b.creation_date DESC ;
如果将条件放在
WHERE
子句中,它将过滤掉所有不匹配的行,从而将LEFT JOIN
变成INNER JOIN
。