mysql - 从子查询中排除记录

Question

我有一个查询，需要从另一个表中删除特定记录。这是我的疑问。以下代码选择用户所在的所有组的所有应用信息。

SELECT   app_name,
         ifnull(max(priv_access), 'N') as priv_access,
         ifnull(max(priv_insert),'N') as priv_insert,
         ifnull(max(priv_delete),'N') as priv_delete,
         ifnull(max(priv_update),'N') as priv_update,
         ifnull(max(priv_export),'N') as priv_export,
         ifnull(max(priv_print),'N') as priv_print
      FROM prismreporting_sec_groups_apps
      WHERE group_id IN
          (SELECT
           group_id
       FROM
           prismreporting_sec_users_groups 
       WHERE
           login = 'kdriscoll')
       group by app_name

我现在想排除该用户的另一个表中的任何记录。我有两个名为“登录”和“应用程序名称”的字段表的名称是 prismreporting_sec_removeuser_apps这是我尝试使用的选择

select app_name from prismreporting_sec_removeuser_apps where login = 'kdriscoll'

我真的不知道用什么方法(如果有的话)我可以做到这一点......有人可以帮忙吗？

提前致谢

凯文

Answer 1

您可以使用 where not in子句:

SELECT   app_name,
     ifnull(max(priv_access), 'N') as priv_access,
     ifnull(max(priv_insert),'N') as priv_insert,
     ifnull(max(priv_delete),'N') as priv_delete,
     ifnull(max(priv_update),'N') as priv_update,
     ifnull(max(priv_export),'N') as priv_export,
     ifnull(max(priv_print),'N') as priv_print
  FROM prismreporting_sec_groups_apps
  WHERE group_id IN
      (SELECT
       group_id
   FROM
       prismreporting_sec_users_groups 
   WHERE
       login = 'kdriscoll'
and app_name not in 
(select app_name from prismreporting_sec_removeuser_apps 
where login = 'kdriscoll')
   group by app_name