> For the complete documentation index, see [llms.txt](https://lei-d.gitbook.io/sql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://lei-d.gitbook.io/sql/leetcode/607sales-person.md).

# 607\_Sales Person

\[easy]

**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`

```
+----------+------+--------+-----------------+-----------+
| 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`

```
+---------+--------+------------+
| 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`

```
+----------+----------+---------+----------+--------+
| 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**

```
+------+
| name | 
+------+
| Amy  | 
| Mark | 
| Alex |
+------+
```

## Solution 1: using join

```php
SELECT s.name
FROM orders o 
    JOIN company c ON o.com_id = c.com_id AND c.name = 'RED'
    RIGHT JOIN salesperson s ON o.sales_id = s.sales_id
WHERE o.sales_id IS NULL;
```

## Solution 2: using subquery

```php
SELECT name
FROM salesperson
WHERE sales_id NOT IN (
    SELECT distinct sales_id
    FROM orders
    WHERE com_id = (SELECT com_id FROM company WHERE name = 'RED')
);
```