I wrote a query joining two tables and I got a below resultset:
SELECT emp.employee_id,
dept.department_name,
dept.department_id
FROM employee emp,
department dept
WHERE emp.department_id = dept.department_id;
Employee_ID Department Department_ID Mark Sales D1 Mark Marketing D2 Justin Textiles D3 Kimberley (null) (null)
However, I need to display below output with one new field called ‘Status’.Mark can work in both the departments and so the count is “2” and the status will be ‘Y’ ( displaying of any one record is okay) . Justin works in only one department and count is 1 and status should be ‘N’. Kimberley does not work anywhere and count is 0 and status should be ‘N’.
Expected output:
Employee_ID Department Department_ID Status Mark Sales D1 Y Justin Textiles D3 N Kimberley (null) (null) N
Please help.
Advertisement
Answer
I understand that you want to display the first department per user, and add a flag that indicates whether the employee belongs to at least one other department.
You can use window functions:
select
employee_id,
department_name,
department_id
case when cnt <= 1 then 'N' else 'Y' end status
from (
select
emp.employee_id,
dept.department_name,
dept.department_id,
row_number() over(partition by emp.employee_id order by dept.department_id) rn,
count(*) over(partition by emp.employee_id) cnt
from
employee emp
left join department dept on emp.department_id = dept.department_id
) t
where rn = 1
Side note: always use explicit joins (with the on keyword) instead of old-school, implicit joins (with commas in the from clause), whose syntax is harder to read and maintain.