﻿select 
ifnull(d1.id, d2.id) DeptID1, 
ifnull(d1.name, d2.name) DeptName1,
if(d1.id is null, null, d2.id) DeptID2, 
if(d1.name is null, null, d2.name) DeptName2,
p.id ProcessID,
p.name ProcessName

from cf_Process p 
left join cf_Department d2 on p.departmentId = d2.id
left join cf_Department d1 on d2.parentId = d1.id
order by DeptName1, DeptName2, ProcessName


----------------------------------------------------------------------


select 
d1.id DeptID1, 
d1.name DeptName1,
d2.id DeptID2, 
d2.name DeptName2,
p1.id ProcessID1,
p1.name ProcessName1,
p2.id ProcessID2,
p2.name ProcessName2

from cf_Department d1
left join cf_Department d2 on d1.id = d2.parentId
left join cf_Process p1 ON d1.id = p1.departmentId
left join cf_Process p2 ON d2.id = p2.departmentId
where d1.parentId = 0
and (
    (p1.id is not null and p2.id is null) 
    or (p1.id is null and p2.id is not null) 
)
order by DeptName1, DeptName2, ProcessName1, ProcessName2


----------------------------------------------------------------------

select 
d1.id DeptID1, 
d1.name DeptName1,
d2.id DeptID2, 
d2.name DeptName2,
p1.id ProcessID1,
p1.name ProcessName1,
p2.id ProcessID2,
p2.name ProcessName2

from cf_Department d1
left join cf_Department d2 on d1.id = d2.parentId
left join cf_Process p1 ON d1.id = p1.departmentId
left join cf_Process p2 ON d2.id = p2.departmentId
where d1.parentId = 0
order by DeptName1, DeptName2, ProcessName1, ProcessName2

