# $1077. 项目员工 III
# https://leetcode-cn.com/problems/project-employees-iii/

# SQL架构
Create table If Not Exists Project (project_id int, employee_id int);
Create table If Not Exists Employee (employee_id int, name varchar(10), experience_years int);
Truncate table Project;
insert into Project (project_id, employee_id) values ('1', '1');
insert into Project (project_id, employee_id) values ('1', '2');
insert into Project (project_id, employee_id) values ('1', '3');
insert into Project (project_id, employee_id) values ('2', '1');
insert into Project (project_id, employee_id) values ('2', '4');
Truncate table Employee;
insert into Employee (employee_id, name, experience_years) values ('1', 'Khaled', '3');
insert into Employee (employee_id, name, experience_years) values ('2', 'Ali', '2');
insert into Employee (employee_id, name, experience_years) values ('3', 'John', '3');
insert into Employee (employee_id, name, experience_years) values ('4', 'Doe', '2');

# Write your MySQL query statement below
select
    p.project_id,
    p.employee_id
from
    Project p
    inner join Employee e on p.employee_id = e.employee_id
where
    (project_id, experience_years) in (
        select
            project_id,
            max(experience_years)
        from
            Project p1
            inner join Employee e1 on p1.employee_id = e1.employee_id
        group by
            project_id
    );

# clean-up
drop table Project;
drop table Employee;