如果与另一个表匹配，则从一个表中排除行-MySql [英] Exclude rows from one table if match on another table - MySql

问题描述

我有两个表:

第一个叫做类别，其中有3列"administrators，id，name".

the first one called categories with 3 columns "administrators,id,name".

administrators | id  | name
1              | 23  | David
2              | 24  | Jemmy
3              | 25  | Frank
NULL           | 26  | GLOBAL1
NULL           | 27  | GLOBAL2
NULL           | 28  | GLOBAL3

第二个表名为 disabledcategories ，其中两列为"administrators"和"id".

The second table is named disabledcategories with two columns "administrators" and "id".

administrators | id 
1              | 26  > ( GLOBAL1 )
1              | 27  > ( GLOBAL2 )

2              | 26  > ( GLOBAL1 )
2              | 27  > ( GLOBAL2 )

3              | 26  > ( GLOBAL1 )
3              | 27  > ( GLOBAL2 )
3              | 28  > ( GLOBAL3 )

我想从类别中选择管理员= LIKE 1或NULL"，而 disabledcategories

And i want to select "administrators = LIKE 1 OR NULL" from categories where there is not in disabledcategories

所需的输出如下:

administrators | id  | name
1              | 23  | David
NULL           | 28  | GLOBAL3

我该怎么做?提前非常感谢您！

How can i do that ? Thank you so much in advance !

推荐答案

从您的示例数据和预期结果中，我认为这是您想要的查询:

From your sample data and expected results I think that this is the query that you want:

select c.* from categories c
where c.administrators = 1
or (
  c.administrators is null
  and c.id not in (
    select id from disabledcategories
    where administrators = 1
  )
);

请参见演示.
结果:

See the demo.
Results:

| administrators | id  | name   |
| -------------- | --- | ------ |
| 1              | 23  | David  |
|                | 28  | GLOBAL |

这篇关于如果与另一个表匹配，则从一个表中排除行-MySql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！