Formatted question description: https://leetcode.ca/all/1077.html

1077. Project Employees III

Level

Medium

Description

Table: Project

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| project_id  | int     |
| employee_id | int     |
+-------------+---------+
(project_id, employee_id) is the primary key of this table.
employee_id is a foreign key to Employee table.

Table: Employee

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+------------------+---------+
employee_id is the primary key of this table.

Write an SQL query that reports the most experienced employees in each project. In case of a tie, report all employees with the maximum number of experience years.

The query result format is in the following example:

Project table: +————-+————-+ | project_id | employee_id | +————-+————-+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 2 | 4 | +————-+————-+

Employee table: +————-+——–+——————+ | employee_id | name | experience_years | +————-+——–+——————+ | 1 | Khaled | 3 | | 2 | Ali | 2 | | 3 | John | 3 | | 4 | Doe | 2 | +————-+——–+——————+

Result table: +————-+—————+ | project_id | employee_id | +————-+—————+ | 1 | 1 | | 1 | 3 | | 2 | 1 | +————-+—————+ Both employees with id 1 and 3 have the most experience among the employees of the first project. For the second project, the employee with id 1 has the most experience.


## Solution
Join the tables `Project` and `Employee` using the field `employee_id`. Select the fields `project_id` and `employee_id` using `max(experience_years)`.
```sql
# Write your MySQL query statement below
select p.project_id, p.employee_id from Project p left join Employee e
    on p.employee_id = e.employee_id
    where (p.project_id, e.experience_years) in (
        select p.project_id, max(e.experience_years) from Project p left join Employee e
                on p.employee_id = e.employee_id
            group by project_id
        );

All Problems

All Solutions