mysql - SQL中基于连接多个表的过滤

Question

我正在使用 MySQL，我有三个表:

Table: Salesperson
ID | Name | Age | Salary
1  | Abe  | 61  | 140000
2  | Bob  | 34  | 44000
5  | Chris| 34  | 40000
7  | Dan  | 41  | 52000
8  | Ken  | 57  | 115000
11 | Joe  | 38  | 38000

Table: Customer
ID | Name     | City     | Industry_Type
4  | Samsonic | pleasant | J
6  | Panasung | oaktown  | J
7  | Samony   | jackson  | B
9  | Orange   | jackson  | B

Table: Orders
Number | order_date | cust_id | salesperson_id | Amount
10     | 1996-08-02 | 4       | 2              |540 
20     | 1999-01-30 | 4       | 8              |1800
30     | 1995-07-14 | 9       | 1              |460
40     | 1998-01-29 | 7       | 2              |2400
50     | 1998-02-03 | 6       | 7              |600
60     | 1998-03-02 | 6       | 7              |720
70     | 1995-05-06 | 9       | 7              |150

我想找到所有没有在 Samsonic 下过任何订单但至少下过 1 个订单的销售人员的姓名

我的预期结果是:

Name
Abe
Dan

我试过:

SELECT Distinct(s.name)
FROM Orders o 
INNER JOIN Salesperson s
ON o.salesperson_id = s.ID
INNER JOIN Customer c
ON c.ID = o.cust_id
WHERE s.name NOT IN(
select s.name where c.name='Samsonic'
);

但是我得到的结果是:

Name
Dan
Bob
Abe

我相信 Bob 出现是因为他下了另一个不是 Samsonic 的订单。如何在仍然加入所有三个表的同时获得预期结果？谢谢!

Answer 1

在您的子查询中，您正在引用外部查询中的表。

下面的代码应该可以解决您的问题。

SELECT DISTINCT s.name
FROM salesperson s 
INNER JOIN orders o ON s.id = o.salesperson_id
INNER JOIN customer c ON o.cust_id = c.id
WHERE s.name NOT IN (
    SELECT s.name
    FROM  salesperson s 
    INNER JOIN orders o ON s.id = o.salesperson_id
    INNER JOIN customer c ON o.cust_id = c.id
    WHERE c.name = 'Samsonic'
);