'SELECT from multiple tables and then JOIN to the first one
I have an existing query, which is selecting the data from 2 tables, then it's joining a third one:
select
p.name as project_name,
e.name as employee_name,
d.name as department_name
from
projects p,
employees e
inner join departments d on d.id = e.department_id
where
e.id = 1 and
p.owner = 'owner 1' and
p.department = 'dept 1';
There is a relation between employees and departments tables, but data from projects has no relation to others and is limited only by WHERE clause.
Now I need to join yet another table but the only possible "anchor" is within projects table.
Obviously another JOIN will not work with the first table listed after FROM clause (projects in this case):
select
p.name as project_name,
e.name as employee_name,
d.name as department_name,
m.name as manager_name
from
projects p,
employees e
inner join departments d on d.id = e.department_id
left join managers m on m.department = p.department
where
e.id = 1 and
p.owner = 'owner 1' and
p.department = 'dept 1';
So how can I join the managers table in this case using the relation between managers.department and projects.department?
Full example can be found on DB Fiddle: https://www.db-fiddle.com/f/kVm9CzuviK7ectSXGe3J48/0
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
