get relation data from one table in oracle [closed]

I have a table, let’s called it data_employee. It contains:

(employee_name, username, department, division, group, job)

and the data is:

(john_doe, john.doe, **it_sec**, null, it, **network_adm**)
(smith_jaeger, smith.jaeger, **it_sec**, null, it, **Department_Head**)

I want to query username’s superior, which is:

if username has department,
then filter their department (e.x: it_sec) and the job department_head
if username has not department,
then filter their division like above and so on deligate until group head

I’ve tried subquery but sometimes they return multiple data, because I have sub group head

and the data also written group head

so, for an example: the result is:

when I want to find john_doe’s department head, the result is smith_jaeger

Answer

Consider this query:

select de1.employee_name, de2.employee_name
from data_employee de1
join data_employee de2
on ((de1.department = de2.department) and (de1.job <> 'Department_Head') and (de2.job = 'Department_Head')) or
((de1.department is null) and (de1.division = de2.division) and (de2.job <> 'Department_Head'))

The first criteria is before the or, the second criteria is after the or. In the first criteria it’s important to specify that an employee is department head and the other is not to avoid having results saying that the department head is his/her own boss. With the second criteria we did not exclude duplicates, because if there could be separate departments with the same division (which should not be the case, but we cannot exclude inconsistency, especially if these are textual data), then the multiple bosses of the separate departments of the same division will appear in the results. Also, if there are multiple department heads, that’s a problem as well.