MySQL (version lower than 8.0) : SELECT WHERE Multiple column and group by max level

Question

I have 2 tables in my database.

1. table of employees in my department. This table is stored centrally. Which I have no right to manage in this table

emp_id	name	job postion	dept_1	dept_2	dept_3
000010	emp1 name1	Director	CEO	Human resource
000012	emp2 name2	employee	CEO	Human resource
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

2. Department table. It's a table that I have created to show my 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

I want to know Which department's employees are under?

I tried using the command :

SELECT *
FROM table1
INNER table2 ON (table2.dept_name = table1.dept_1 OR table2.dept_name = table1.dept_2 OR table2.dept_name = table1.dept_3)

But it's not the answer I want.

I want

emp_id	name	job postion	dept_level	dept_name	dept_order
000010	emp1 name1	Director	2	Human Resource	2
000012	emp2 name2	employee	2	Human Resource	2
000013	emp2 name2	Director	3	Recruitment	3
000200	emp2 name2	Head Director	1	CEO	1

What should I do?

P.S. Sorry my english

Answer 1

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

For MySQL 5.7 / MariaDB 10.1 use

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

fiddle