我正在尝试使用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

08-25 15:50
查看更多