Primary Department for Each Employee
Task: Report all the employees with their primary department. For employees who belong to one department, report their only department.
Table: Employee
+---------------+------------+
| Column Name | Type |
+---------------+------------+
| employee_id | INT |
| department_id | INT |
| primary_flag | varchar(1) |
+---------------+------------+Input: Employee table:
+-------------+---------------+--------------+
| employee_id | department_id | primary_flag |
+-------------+---------------+--------------+
| 1 | 1 | N |
| 2 | 1 | Y |
| 2 | 2 | N |
| 3 | 3 | N |
| 4 | 2 | N |
| 4 | 3 | Y |
| 4 | 4 | N |
+-------------+---------------+--------------+Output:
+-------------+---------------+
| employee_id | department_id |
+-------------+---------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 3 |
| 4 | 3 |
+-------------+---------------+View Reference Solution & Explanation ▼
This solution uses the `GROUP BY` clause to aggregate results, a `HAVING` clause to filter the aggregated groups, and a subquery to filter records based on another query's result.
SELECT employee_id, department_id FROM Employee WHERE primary_flag = 'Y' OR employee_id IN (SELECT employee_id FROM Employee GROUP BY employee_id HAVING COUNT(*) = 1);