607. Sales Person
Problem Statement
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.
Table Schema
Table: SalesPerson
+-----------------+---------+
| sales_id | int |
| name | varchar |
| salary | int |
| commission_rate | int |
| hire_date | date |
+-----------------+---------+
sales_id is the primary key.
Table: Company
+-------------+---------+
| com_id | int |
| name | varchar |
| city | varchar |
+-------------+---------+
com_id is the primary key.
Table: Orders
+-------------+------+
| order_id | int |
| order_date | date |
| com_id | int |
| sales_id | int |
| amount | int |
+-------------+------+
order_id is the primary key.
Examples
Example 1
Input Table:
SalesPerson table:
+----------+------+--------+-----------------+------------+
| sales_id | name | salary | commission_rate | hire_date |
+----------+------+--------+-----------------+------------+
| 1 | John | 100000 | 6 | 2006-04-01 |
| 2 | Amy | 12000 | 5 | 2010-05-01 |
+----------+------+--------+-----------------+------------+
Company table:
+--------+------+----------+
| com_id | name | city |
+--------+------+----------+
| 1 | RED | Boston |
| 2 | ORG | New York |
+--------+------+----------+
Orders table:
+----------+------------+--------+----------+--------+
| order_id | order_date | com_id | sales_id | amount |
+----------+------------+--------+----------+--------+
| 1 | 2014-01-01 | 3 | 4 | 10000 |
| 2 | 2014-02-01 | 1 | 1 | 5000 |
+----------+------------+--------+----------+--------+
Expected Output:
+------+
| name |
+------+
| Amy |
+------+
SQL Solution
SELECT name
FROM SalesPerson
WHERE sales_id NOT IN (
SELECT o.sales_id
FROM Orders o
JOIN Company c ON o.com_id = c.com_id
WHERE c.name = 'RED'
);
Problem Info
DifficultyEASY
Topics
subqueryjoinnot-in
Reference Links