MySQL 8.0

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