You have tables: employees(id, name, department_id), departments(id, dept_name), projects(id, dept_id, project_name), tasks(id, project_id, task_name). Write a query to list all employees with their department, projects, and tasks. Include employees even if they have no department, projects, or tasks. Which query achieves this?
ASELECT e.name, d.dept_name, p.project_name, t.task_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id LEFT JOIN projects p ON d.id = p.dept_id LEFT JOIN tasks t ON p.id = t.project_id;
BSELECT e.name, d.dept_name, p.project_name, t.task_name FROM employees e LEFT JOIN departments d ON e.id = d.id LEFT JOIN projects p ON d.id = p.dept_id LEFT JOIN tasks t ON p.id = t.project_id;
CSELECT e.name, d.dept_name, p.project_name, t.task_name FROM employees e INNER JOIN departments d ON e.id = d.id INNER JOIN projects p ON d.id = p.dept_id INNER JOIN tasks t ON p.id = t.project_id;
DSELECT e.name, d.dept_name, p.project_name, t.task_name FROM employees e LEFT JOIN departments d ON e.id = d.id LEFT JOIN projects p ON e.id = p.dept_id LEFT JOIN tasks t ON p.id = t.project_id;
Step-by-Step Solution
Solution:
Step 1: Identify correct JOIN keys
Employees link to departments by e.department_id = d.id, not e.id = d.id. Projects link to departments by dept_id, tasks to projects by project_id.
Step 2: Use LEFT JOINs to keep all employees
LEFT JOINs keep all employees even if no department, projects, or tasks exist.
Step 3: Check each option
SELECT e.name, d.dept_name, p.project_name, t.task_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id LEFT JOIN projects p ON d.id = p.dept_id LEFT JOIN tasks t ON p.id = t.project_id; uses correct keys and LEFT JOINs. Other queries use wrong keys (joining on e.id = d.id or e.id = p.dept_id) or INNER JOINs, excluding employees without matches.
Final Answer:
SELECT e.name, d.dept_name, p.project_name, t.task_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id LEFT JOIN projects p ON d.id = p.dept_id LEFT JOIN tasks t ON p.id = t.project_id; -> Option A
Quick Check:
Correct keys + LEFT JOINs keep all employees [OK]
Quick Trick:Match keys correctly and use LEFT JOINs to keep all main rows [OK]
Common Mistakes:
MISTAKES
Joining on wrong columns causing missing data
Using INNER JOINs losing employees without projects
Confusing employee ID with department ID
Master "LEFT and RIGHT JOIN" in SQL
9 interactive learning modes - each teaches the same concept differently
