607_Sales Person
[easy]
Description
Given three tables:salesperson
,company
,orders
.
Output all thenamesin the tablesalesperson
, 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
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
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')
);
Last updated
Was this helpful?