在SQL中基于联接多个表进行过滤 [英] Filtering based on Joining Multiple Tables in SQL

问题描述

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

I am using MySQL and I have three table:

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的销售人员的姓名

I want to find the names of all salespeople that do not have any orders with Samsonic and who have made at least 1 order

我的预期结果是:

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的订单.在仍然连接所有三个表的同时如何获得预期的结果?谢谢！

I believe Bob shows up since he made another order that was not Samsonic. How can I get my intended result while still joining all three tables? Thanks!

推荐答案

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

In your subquery, you are referencing tables in the outer query.

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

The code below should fix your issue.

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'
);

这篇关于在SQL中基于联接多个表进行过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！