mysql - 不等于 LeftJoin 的搜索

Question

我有一个问题

Select 
    m.name 

from machines m 
    innerJoin config c ON m.cod = c.cod

--> where ( c.category = 'BIOS' and c.val = 'System' )  
--> and   ( c.category = 'Users' and c.val = 'Jonas' )  

group by c.cod

* MACHINES ---------------- +
| key    | name    | cod    |
* ------ + ------- + ------ +
| 1      | M1      | 23     |
| 2      | M2      | 26     |
| 3      | M3      | 27     |
+ ------ + ------- + ------ +

* CONFIG --------------------------- +
| key    | category | cod   | val    |
+ ------ + -------- + ----- + ------ +
| 1      | BIOS     | 23    | System |
| 2      | Users    | 23    | Jonas  |
| 3      | Users    | 23    | Maria  |
| 4      | BIOS     | 26    | System |
| 5      | Users    | 26    | Jonas  |
| 6      | BIOS     | 27    | System |
| 6      | Users    | 27    | Ana    |
+ ------ + -------- + ----- + ------ +

我得到了

* RESULT -- +
| name      |
+ --------- +
| M1        |
| M2        |
+ --------- +

它非常适合“等于”搜索，但我不知道如何针对此查询执行“不等于”搜索。

我试过:

Select 
    m.name 

from machines m 
    innerJoin config c ON m.cod = c.cod

--> where ( c.category = 'BIOS' and c.val <> 'System' )  
--> or    ( c.category = 'Users' and c.val <> 'Jonas' )  

group by c.cod

但仍然得到机器“M1”，值为“Maria”的字段与过滤器匹配

我需要用这个过滤器来处理结果:

* RESULT -- +
| name      |
+ --------- +
| M3        |
+ --------- +

我该怎么做？

Answer 1

试试这个:

SELECT name FROM machines 
WHERE name NOT IN (
SELECT m.name FROM machines m    INNER JOIN config c ON m.cod = c.cod WHERE ( c.category = 'BIOS' and c.val = 'System' )  AND ( c.category = 'Users' and c.val = 'Jonas' ) GROUP BY c.cod)