SQL: Select from multiple JOINS in different subqueries

Question

I have the two tables Employees and Assignments with contents as visible in the images below (output A and B respectively). Each employee in Employees has max. 1 project leader (Assignment.A_Leader = 1) and max. 1 project controller (Assignment.A_Controller = 1) assignment. I want to get a list of all employees with the projects they are leaders or controllers of. If there is no such assignment for an employee, NULL should appear. I manage to list either leader or project assignments. However, I don't manage to get both in a proper list together as suggested here (see attempt E). What do I have to add in order to get the desired final table?

The letters A-E for the codes below correspond to the letters A-E for the outputs in the image.

I am using Microsoft SQL Server 2017.

-- A: all employees
SELECT * FROM Employee

-- B: all assignments
SELECT * FROM Assignment

-- C: employees with their projects as LEADERS
SELECT E.E_FirstName as First, E.E_LastName as Last, L.A_ProjectID as Leader
FROM Employee E LEFT JOIN
(SELECT A.E_ID, A_ProjectID FROM Assignment A WHERE A.A_Leader=1) L
ON E.E_ID = L.E_ID

-- D: employees with their projects as CONTROLLERS
SELECT E.E_FirstName as First, E.E_LastName as Last, C.A_ProjectID as Controller
FROM Employee E LEFT JOIN
(SELECT A.E_ID, A_ProjectID FROM Assignment A WHERE A.A_Controller=1) C
ON E.E_ID = C.E_ID

-- E: employees with their projects as LEADERS or CONTROLLERS
SELECT X.First, X.Last, X.Leader, Y.Controller FROM
(SELECT E.E_FirstName as First, E.E_LastName as Last, L.A_ProjectID as Leader
FROM Employee E LEFT JOIN
(SELECT A.E_ID, A_ProjectID FROM Assignment A WHERE A.A_Leader=1) L
ON E.E_ID = L.E_ID) as X,
(SELECT E.E_FirstName as First, E.E_LastName as Last, C.A_ProjectID as Controller
FROM Employee E LEFT JOIN
(SELECT A.E_ID, A_ProjectID FROM Assignment A WHERE A.A_Controller=1) C
ON E.E_ID = C.E_ID) as Y

Answer 1

You can just left join the assignments table twice:

select
    e.e_first_name,
    e.e_last_name,
    al.a_project_id leader,
    ac.a_project_id controller
from employee e
left join assignments al on al.e_id = e.e_id and al.a_leader     = 1
left join assignments ac on ac.e_id = e.e_id and ac.a_controller = 1