Sales Person Problem


Description

LeetCode Problem 607.

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
10
+----------+------+--------+-----------------+-----------+
| 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
9
+---------+--------+------------+
| 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
9
+----------+------------+---------+----------+--------+
| order_id | order_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 ‘Pam’ have sales to company ‘RED’, so we need to output all the other names in the table salesperson


MySQL Solution

1
2
3
4
5
6
7
select s.name
from salesperson s
where s.sales_id not in 
    (select o.sales_id
    from orders o
    left join company c on o.com_id = c.com_id
    where c.name = 'RED')




Related Posts

Human Traffic Of Stadium Problem

LeetCode 601. Write an SQL query to display the records...

Friend Requests II: Who Has The Most Friends Problem

LeetCode 602. In social network like Facebook or Twitter, people...

Tree Node Problem

LeetCode 608. Given a table tree, id is identifier of...

Friend Requests I: Overall Acceptance Rate Problem

LeetCode 597. Write an SQL query to find the overall...

Sales Person Problem

LeetCode 607. Given three tables, salesperson, company, orders.

Consecutive Available Seats Problem

LeetCode 603. Several friends at a cinema ticket office would...