Amulya Sharma Amulya Sharma - 3 months ago 10
SQL Question

Unable to make desired JOINS between multiple tables

I've got 3 tables

Plan:

plan_id emp_id duration
123 1010 30
456 1011 40
789 1012 60


PlanEmp:

plan_id emp_id
123 2131
456 3131
789 4131


Emp:

emp_id Name
1010 Andy
1011 Cole
1012 John
2131 Sam
3131 Kim
4131 Ray


Desired Summary Output:

plan_id Name duration
123 Andy 30
123 Sam 30
456 Cole 40
456 Kim 40
789 John 60
789 Ray 60


Query that I'm trying to modify to get the above result:

SELECT P.plan_id
,E.Name
,P.duration
FROM Plan P
LEFT JOIN Emp E
ON P.emp_id = E.emp_id
LEFT JOIN PlanEmp PE
ON P.plan_id = PE.plan_id


I'm unable to figure how to pull the Employee details using the PlanEmp table AND the Plan table to get the summary output.

Answer

This should work:

SELECT sub.plan_id, sub.emp_id, Emp.name, Plan.duration
FROM
(SELECT plan_id, emp_id
FROM Plan
UNION
SELECT plan_id, emp_id
FROM PlanEmp) sub
LEFT JOIN Emp
ON sub.emp_id = Emp.emp_id
LEFT JOIN Plan
ON sub.plan_id = Plan.plan_id
ORDER BY plan_id

Tested here: http://sqlfiddle.com/#!9/21ca79/4