By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE employees ( emp_id VARCHAR(255),
name VARCHAR(255),
job_postion VARCHAR(255),
dept_1 VARCHAR(255),
dept_2 VARCHAR(255),
dept_3 VARCHAR(255) );
INSERT INTO employees VALUES
('000010','emp1 name1','Director','CEO','Human resource',NULL),
('000012','emp2 name2','employee','CEO','Human resource',NULL),
('000013','emp2 name2','Director','CEO','Human resource','Recruitment'),
('000014','emp2 name2','employee','CEO','Human resource','Recruitment'),
('000015','emp2 name2','employee','CEO','Human resource','Recruitment'),
('000200','emp2 name2','Head Director','CEO',NULL,NULL);
SELECT * FROM employees;
| emp_id | name | job_postion | dept_1 | dept_2 | dept_3 |
|---|---|---|---|---|---|
| 000010 | emp1 name1 | Director | CEO | Human resource | null |
| 000012 | emp2 name2 | employee | CEO | Human resource | null |
| 000013 | emp2 name2 | Director | CEO | Human resource | Recruitment |
| 000014 | emp2 name2 | employee | CEO | Human resource | Recruitment |
| 000015 | emp2 name2 | employee | CEO | Human resource | Recruitment |
| 000200 | emp2 name2 | Head Director | CEO | null | null |
CREATE TABLE department ( dept_id INT,
dept_level INT,
dept_name VARCHAR(255),
dept_order INT );
INSERT INTO department VALUES
(1,1,'CEO',1),
(2,2,'Human Resource',2),
(2,3,'Recruitment',3),
(2,3,'Training',4),
(2,2,'Sale',5),
(2,3,'Sale planning',6),
(2,3,'Sale marketing',7);
SELECT * FROM department;
| dept_id | dept_level | dept_name | dept_order |
|---|---|---|---|
| 1 | 1 | CEO | 1 |
| 2 | 2 | Human Resource | 2 |
| 2 | 3 | Recruitment | 3 |
| 2 | 3 | Training | 4 |
| 2 | 2 | Sale | 5 |
| 2 | 3 | Sale planning | 6 |
| 2 | 3 | Sale marketing | 7 |
WITH cte AS ( SELECT *, MAX(d.dept_level) OVER (PARTITION BY e.emp_id) maxlevel
FROM employees e
JOIN department d ON d.dept_name IN (e.dept_1, e.dept_2, e.dept_3) )
SELECT *
FROM cte
WHERE dept_level = maxlevel
ORDER BY emp_id
| emp_id | name | job_postion | dept_1 | dept_2 | dept_3 | dept_id | dept_level | dept_name | dept_order | maxlevel |
|---|---|---|---|---|---|---|---|---|---|---|
| 000010 | emp1 name1 | Director | CEO | Human resource | null | 2 | 2 | Human Resource | 2 | 2 |
| 000012 | emp2 name2 | employee | CEO | Human resource | null | 2 | 2 | Human Resource | 2 | 2 |
| 000013 | emp2 name2 | Director | CEO | Human resource | Recruitment | 2 | 3 | Recruitment | 3 | 3 |
| 000014 | emp2 name2 | employee | CEO | Human resource | Recruitment | 2 | 3 | Recruitment | 3 | 3 |
| 000015 | emp2 name2 | employee | CEO | Human resource | Recruitment | 2 | 3 | Recruitment | 3 | 3 |
| 000200 | emp2 name2 | Head Director | CEO | null | null | 1 | 1 | CEO | 1 | 1 |
SELECT *
FROM employees e
JOIN department d ON d.dept_name IN (e.dept_1, e.dept_2, e.dept_3)
JOIN ( SELECT e.emp_id,
MAX(d.dept_level) maxlevel
FROM employees e
JOIN department d ON d.dept_name IN (e.dept_1, e.dept_2, e.dept_3)
GROUP BY e.emp_id ) ed USING (emp_id)
WHERE d.dept_level = ed.maxlevel
ORDER BY emp_id
| emp_id | name | job_postion | dept_1 | dept_2 | dept_3 | dept_id | dept_level | dept_name | dept_order | maxlevel |
|---|---|---|---|---|---|---|---|---|---|---|
| 000010 | emp1 name1 | Director | CEO | Human resource | null | 2 | 2 | Human Resource | 2 | 2 |
| 000012 | emp2 name2 | employee | CEO | Human resource | null | 2 | 2 | Human Resource | 2 | 2 |
| 000013 | emp2 name2 | Director | CEO | Human resource | Recruitment | 2 | 3 | Recruitment | 3 | 3 |
| 000014 | emp2 name2 | employee | CEO | Human resource | Recruitment | 2 | 3 | Recruitment | 3 | 3 |
| 000015 | emp2 name2 | employee | CEO | Human resource | Recruitment | 2 | 3 | Recruitment | 3 | 3 |
| 000200 | emp2 name2 | Head Director | CEO | null | null | 1 | 1 | CEO | 1 | 1 |