# 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')
);
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://lei-d.gitbook.io/sql/leetcode/607sales-person.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.