在SQL中基于联接多个表进行过滤

本文介绍了在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' );