mysql join 替代办法_mysql – 如何用JOIN替换NOT EXISTS？

我有以下查询：

select distinct a.id,a.name

from Employee a

join Dependencies b on a.id = b.eid

where not exists

(

select *

from Dependencies d

where b.id = d.id

and d.name = 'Apple'

)

and exists

(

select *

from Dependencies c

where b.id = c.id

and c.name = 'Orange'

);

我有两张桌子,比较简单.

第一个Employee有一个id列和一个name列

第二个表Dependencies有3列,一个id,一个eid(要链接的员工ID)和名称(apple,orange等).

数据看起来像这样

Employee表看起来像这样

id | name

-----------

1 | Pat

2 | Tom

3 | Rob

4 | Sam

依赖

id | eid | Name

--------------------

1 | 1 | Orange

2 | 1 | Apple

3 | 2 | Strawberry

4 | 2 | Apple

5 | 3 | Orange

6 | 3 | Banana

正如你所看到的,帕特同时拥有Orange和Apple,他需要被排除在外,它必须是通过连接,我似乎无法让它工作.最终数据应该只返回Rob