Given tables Employees (EmployeeID primary key), Projects (ProjectID primary key), and Assignments (EmployeeID and ProjectID foreign keys), how do you find all employees and their projects, including employees with no projects?
hard📝 Application Q9 of 15
SQL - INNER JOIN
Given tables Employees (EmployeeID primary key), Projects (ProjectID primary key), and Assignments (EmployeeID and ProjectID foreign keys), how do you find all employees and their projects, including employees with no projects?
ASELECT E.Name, P.ProjectName FROM Employees E JOIN Assignments A ON E.EmployeeID = A.EmployeeID JOIN Projects P ON A.ProjectID = P.ProjectID;
BSELECT E.Name, P.ProjectName FROM Employees E LEFT JOIN Assignments A ON E.EmployeeID = A.EmployeeID LEFT JOIN Projects P ON A.ProjectID = P.ProjectID;
CSELECT E.Name, P.ProjectName FROM Projects P LEFT JOIN Assignments A ON P.ProjectID = A.ProjectID LEFT JOIN Employees E ON A.EmployeeID = E.EmployeeID;
DSELECT E.Name, P.ProjectName FROM Assignments A JOIN Employees E ON A.EmployeeID = E.EmployeeID JOIN Projects P ON A.ProjectID = P.ProjectID;
Step-by-Step Solution
Solution:
Step 1: Use LEFT JOIN from Employees to Assignments
LEFT JOIN keeps all employees, even those without assignments.
Step 2: LEFT JOIN Assignments to Projects
To get project names, join assignments to projects, allowing NULL for no projects.
Step 3: Select employee names and project names
This returns all employees with their projects or NULL if none.
Final Answer:
SELECT E.Name, P.ProjectName FROM Employees E LEFT JOIN Assignments A ON E.EmployeeID = A.EmployeeID LEFT JOIN Projects P ON A.ProjectID = P.ProjectID; -> Option B
Quick Check:
LEFT JOINs include all employees even without projects [OK]
Quick Trick:Use LEFT JOIN to include all from main table [OK]
Common Mistakes:
MISTAKES
Using INNER JOIN excludes employees without projects
Joining tables in wrong order
Omitting one join needed for project info
Master "INNER JOIN" in SQL
9 interactive learning modes - each teaches the same concept differently
