By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Employee
(
employee_id INT,
department_id int,
primary_flag CHAR(1)
);
INSERT INTO Employee (employee_id, department_id, primary_flag) VALUES
('1', '1', 'N'),
('2', '1', 'Y'),
('2', '2', 'N'),
('3', '3', 'N'),
('4', '2', 'N'),
('4', '3', 'Y'),
('4', '4', 'N');
Records: 7 Duplicates: 0 Warnings: 0
SELECT
e.employee_id,
CASE WHEN COUNT(*) = 1
THEN MIN(e.department_id)
ELSE MIN(CASE WHEN e.primary_flag = 'Y' THEN e.department_id END)
END AS department_id
FROM Employee e
GROUP BY
e.employee_id;
| employee_id | department_id |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 3 |
| 4 | 3 |
select employee_id, department_id
from Employee
WHERE (employee_id, department_id) not in(
select employee_id, department_id
from (
SELECT employee_id, department_id, primary_flag,
count(employee_id) over (partition by employee_id) as cnt
from Employee
) a
WHERE cnt > 1 and primary_flag = 'n'
);
| employee_id | department_id |
|---|
alter table Employee modify employee_id int not null;
alter table Employee modify department_id int not null;
Records: 0 Duplicates: 0 Warnings: 0
Records: 0 Duplicates: 0 Warnings: 0
select employee_id, department_id
from Employee
WHERE (employee_id, department_id) not in(
select employee_id, department_id
from (
SELECT employee_id, department_id, primary_flag,
count(employee_id) over (partition by employee_id) as cnt
from Employee
) a
WHERE cnt > 1 and primary_flag = 'n'
);
| employee_id | department_id |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 3 |
| 4 | 3 |