假设我有三个表：
位置-一家商店可以属于多个地点，而一个地点可以有多个商店
+-------------+----------+ | LOCATION_ID | STORE_ID | +-------------+----------+
STORES - only one row per store, every store has only one manager ID
+----------+------------+ | STORE_ID | MANAGER_ID | +----------+------------+
EMPLOYEES - a manager can have multiple employees, and employees can belong to more than one manager
+-------------+-------------+ | MANAGER_ID | EMPLOYEE_ID | +-------------+-------------+
And for a given location (e.g. LOCATION_ID = 999), I want to get all the employees managed at stores at that location.
This gets me the list of managers that belong to stores in that location:
SELECT s.MANAGER_ID FROM LOCATIONS l
INNER JOIN STORES s
ON s.STORE_ID = l.STORE_ID
WHERE l.LOCATION_ID = 999;
我真正想要的是链接到查询吐出的管理器的所有不同的EMPLOYEE_ID。
我可以在同一查询中添加哪些其他联接来获得该联接？
最佳答案
SELECT DISTINCT E.EMPLOYEE_ID
FROM LOCATIONS L
INNER JOIN STORES S ON S.STORE_ID = L.STORE_ID
INNER JOIN EMPLOYEES E ON S.MANAGER_ID = E.MANAGER_ID
WHERE L.LOCATION_ID = 999;