LeetCode: Sales Person

LeetCode: Sales Person

Description

Given three tables: salesperson, company, orders. Output all the names in the table salesperson, who didn’t have sales to company ‘RED’.

Example

Input

Table: salesperson

1
2
3
4
5
6
7
8
9
+----------+------+--------+-----------------+-----------+
| sales_id | name | salary | commission_rate | hire_date |
+----------+------+--------+-----------------+-----------+
|   1      | John | 100000 |     6           | 4/1/2006  |
|   2      | Amy  | 120000 |     5           | 5/1/2010  |
|   3      | Mark | 65000  |     12          | 12/25/2008|
|   4      | Pam  | 25000  |     25          | 1/1/2005  |
|   5      | Alex | 50000  |     10          | 2/3/2007  |
+----------+------+--------+-----------------+-----------+

The table salesperson holds the salesperson information. Every salesperson has a sales_id and a name. Table: company

1
2
3
4
5
6
7
8
+---------+--------+------------+
| com_id  |  name  |    city    |
+---------+--------+------------+
|   1     |  RED   |   Boston   |
|   2     | ORANGE |   New York |
|   3     | YELLOW |   Boston   |
|   4     | GREEN  |   Austin   |
+---------+--------+------------+

The table company holds the company information. Every company has a com_id and a name. Table: orders

1
2
3
4
5
6
7
8
+----------+----------+---------+----------+--------+
| order_id |  date    | com_id  | sales_id | amount |
+----------+----------+---------+----------+--------+
| 1        | 1/1/2014 |    3    |    4     | 100000 |
| 2        | 2/1/2014 |    4    |    5     | 5000   |
| 3        | 3/1/2014 |    1    |    1     | 50000  |
| 4        | 4/1/2014 |    1    |    4     | 25000  |
+----------+----------+---------+----------+--------+

The table orders holds the sales record information, salesperson and customer company are represented by sales_id and com_id.

output

1
2
3
4
5
6
7
+------+
| name |
+------+
| Amy  |
| Mark |
| Alex |
+------+

Explanation

According to order ‘3’ and ‘4’ in table orders, it is easy to tell only salesperson ‘John’ and ‘Alex’ have sales to company ‘RED’, so we need to output all the other names in table salesperson.

1
2
3
4
5
6
7
8
9
# Write your MySQL query statement below

select s.name from salesperson s where s.sales_id not in
(
    select salesperson.sales_id from salesperson
    join orders on salesperson.sales_id = orders.sales_id
    join company on company.com_id = orders.com_id
    where company.name = "RED"
)