mysql对表联查排除另一个表,如果与另一个表匹配，则从一个表中排除行-MySql

I have two tables :

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

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 )

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

Desired Output like this :

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 |