如果与另一个表匹配,则从一个表中排除行-MySql [英] Exclude rows from one table if match on another table - MySql
本文介绍了如果与另一个表匹配,则从一个表中排除行-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屋!
查看全文