SQLite多子查询逻辑

Question

问题是要求一个人编写一个查询来查找员工的姓名(first_name，last_name)，这些员工的经理在美国的一个部门工作。这里有一个指向问题的链接，以查看表格，https://www.w3resource.com/sqlite-exercises/sqlite-subquery-exercise-3.php。

对于子查询，我在department和location表之间的位置id上进行了左连接，然后选择'US‘作为country_id，并返回manager_id

对于外部查询，我选择了Employee表，并从子查询列表中选择了manager_ids。

    SELECT first_name, last_name
    FROM Employees
    WHERE manager_id IN (SELECT manager_id
        FROM Departments d LEFT JOIN Locations l ON d.location_id = l.location_id
        WHERE country_id = 'US')
    ORDER BY first_name;

使用我的代码，我没有得到正确的答案，结果与网站上显示的结果集/输出相同。在正确答案中总共有三个子查询。我不明白包含employees表的子查询(最外层的子查询)的目的是什么。我明白这就是我搞砸的地方，但我不明白为什么。

    SELECT first_name, last_name 
    FROM employees 
    WHERE manager_id IN 
       (SELECT employee_id 
        FROM employees 
        WHERE department_id IN 
           (SELECT department_id 
            FROM departments 
            WHERE location_id IN 
                (SELECT location_id 
                 FROM locations 
                 WHERE country_id='US')));

Answer 1

你需要连接所有的表：

select e.first_name, e.last_name
from employees e 
inner join employees m on m.employee_id = e.manager_id 
inner join departments d on d.department_id = m.department_id
inner join locations l on l.location_id = d.location_id
where l.country_id='US'