[LeetCode][MySQL] 607. Sales Person

Easy 🔗607. 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.

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.

✏️문제 풀이

SELECT

SELECT name

• name : 판매자 이릅

FROM

FROM SalesPerson

WHERE

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

• INNER JOIN으로 SalesPerson, Company, Orders 테이블을 합쳐 준 후에 Company 테이블의 이름(name)이 RED인 데이터를 먼저 추출
• SalesPerson 테이블의 name이 여기에 해당하지 않는 경우가 정답이기 때문에 NOT IN을 사용

💯제출 코드

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