# 607. Sales Person

> Nguồn: https://tiennhm.io.vn/docs/leetcode/database/607-sales-person
> Write a solution to find the names of all the salespersons who did not have any orders related to the company with the name `RED`.

## Giới thiệu bài toán

Chi tiết: https://leetcode.com/problems/sales-person/

### Table `SalesPerson`

| Column Name     | Type    |
| --------------- | ------- |
| sales_id        | int     |
| name            | varchar |
| salary          | int     |
| commission_rate | int     |
| hire_date       | date    |

- `sales_id` is the primary key (column with unique values) for this table.
- Each row of this table indicates the name and the ID of a salesperson alongside their salary, commission rate, and hire date.

### Table `Company`

| Column Name | Type    |
| ----------- | ------- |
| com_id      | int     |
| name        | varchar |
| city        | varchar |

- `com_id` is the primary key (column with unique values) for this table.
- Each row of this table indicates the name and the ID of a company and the city in which the company is located.

### Table `Orders`

| Column Name | Type |
| ----------- | ---- |
| order_id    | int  |
| order_date  | date |
| com_id      | int  |
| sales_id    | int  |
| amount      | int  |

- `order_id` is the primary key (column with unique values) for this table.
- `com_id` is a foreign key (reference column) to com_id from the Company table.
- `sales_id` is a foreign key (reference column) to sales_id from the SalesPerson table.
- Each row of this table contains information about one order. This includes the ID of the company, the ID of the salesperson, the date of the order, and the amount paid.

### Yêu cầu
> **Tip: Yêu cầu**
>
> Write a solution to find the names of all the salespersons who did not have any orders related to the company with the name `RED`.
>
> Return the result table in any order. The result format is in the following example.
## Ví dụ

### Input

- `SalesPerson` table:

| sales_id | name | salary | commission_rate | hire_date  |
| -------- | ---- | ------ | --------------- | ---------- |
| 1        | John | 100000 | 6               | 4/1/2006   |
| 2        | Amy  | 12000  | 5               | 5/1/2010   |
| 3        | Mark | 65000  | 12              | 12/25/2008 |
| 4        | Pam  | 25000  | 25              | 1/1/2005   |
| 5        | Alex | 5000   | 10              | 2/3/2007   |

- `Company` table:

| com_id | name   | city     |
| ------ | ------ | -------- |
| 1      | RED    | Boston   |
| 2      | ORANGE | New York |
| 3      | YELLOW | Boston   |
| 4      | GREEN  | Austin   |

- `Orders` table:

| order_id | order_date | com_id | sales_id | amount |
| -------- | ---------- | ------ | -------- | ------ |
| 1        | 1/1/2014   | 3      | 4        | 10000  |
| 2        | 2/1/2014   | 4      | 5        | 5000   |
| 3        | 3/1/2014   | 1      | 1        | 50000  |
| 4        | 4/1/2014   | 1      | 4        | 25000  |

### Output:

| name |
| ---- |
| Amy  |
| Mark |
| Alex |

### Explanation:

According to orders 3 and 4 in the Orders table, it is easy to tell that only salesperson John and Pam have sales to company `RED`, so we report all the other names in the table salesperson.

## Giải quyết bài toán

```sql
SELECT sp.name
FROM SalesPerson sp
WHERE sp.sales_id NOT IN (
    SELECT o.sales_id
    FROM Orders o INNER JOIN Company c ON o.com_id = c.com_id
    WHERE c.name = 'RED'
);
```

Tham khảo: https://leetcode.com/submissions/detail/1056143690/