How can I change the select to get all data in one block of rows?

Question

I want to get employees information's and Head of department name and custody officer I did this query :

SELECT c.EMP_JOB_NO,
       f.ITEM_DESC_A ,
       e.DEPT_NAME_A as "emp_dept",
       a.DEPT_ID,
    b.DEPT_NAME_A as "clear_depts", 
    c.emp_name_a ,
    a.APPROVE_HOD  ,
    a.HOD_ID , null as "Head Name" , a.CUST_ID , null "cust name"
    FROM CLEARANCE_ORDERS_CUST a,
    CLEARANCE_DEPARTMENTS b , 
    clearance_emp_info c , 
    clearance_orders d , 
    clearance_departments e ,
    sys_codes f
WHERE a.DEPT_ID = b.DEPT_ID
    -- and c.emp_id = a.hod_id
     and a.orders_id = 81
     and d.ORDER_NO = a.ORDERS_ID
     and d.EMP_NO = c.EMP_ID 
     and e.DEPT_ID = c.DEPT_ID
     and f.ITEM_NO = c.NATIONALITY
     and f.TABLE_NO = 108
     and f.HOSPITAL_NO = 720022
     union 
SELECT null,
       null ,
       null as "emp_dept",
       null,
       null as "clear_depts", 
       null ,
       null  ,
      null , 
      c.emp_name_a as "Head Name" , 
     null , 
     null 
    FROM CLEARANCE_ORDERS_CUST a,
    CLEARANCE_DEPARTMENTS b , 
    clearance_emp_info c , 
    clearance_orders d , 
    clearance_departments e ,
    sys_codes f
WHERE a.DEPT_ID = b.DEPT_ID
     and c.emp_id = a.hod_id
     and a.orders_id = 81
     and d.ORDER_NO = a.ORDERS_ID
    -- and d.EMP_NO = c.EMP_ID 
     and e.DEPT_ID = c.DEPT_ID
     and f.ITEM_NO = c.NATIONALITY
     and f.TABLE_NO = 108
     and f.HOSPITAL_NO = 720022
     union 
SELECT null,
       null ,
       null as "emp_dept",
       null,
       null as "clear_depts", 
       null ,
       null  ,
      null , 
      null as "Head Name" , 
     null , 
     c.emp_name_a as "cust name"
    FROM CLEARANCE_ORDERS_CUST a,
    CLEARANCE_DEPARTMENTS b , 
    clearance_emp_info c , 
    clearance_orders d , 
    clearance_departments e ,
    sys_codes f
WHERE a.DEPT_ID = b.DEPT_ID
     and c.emp_id = a.CUST_ID
     and a.orders_id = 81
     and d.ORDER_NO = a.ORDERS_ID
    -- and d.EMP_NO = c.EMP_ID 
     and e.DEPT_ID = c.DEPT_ID
     and f.ITEM_NO = c.NATIONALITY
     and f.TABLE_NO = 108
     and f.HOSPITAL_NO = 720022  

now the output divided into 3 parts one part for each select how can I concatenate the 3 select in one select and get all data in same rows , the difference between 3 select was I need to get employee name and department manager name and custody officer name

this condition different in each select

and d.EMP_NO = c.EMP_ID 

and c.emp_id = a.hod_id

and c.emp_id = a.CUST_ID

can I do one select and collect three conditions ?

Answer 1

From Oracle 12, use a LATERAL join and CASE expressions:

SELECT CASE j.type WHEN 1 THEN c.EMP_JOB_NO  END AS EMP_JOB_NO,
       CASE j.type WHEN 1 THEN f.ITEM_DESC_A END AS ITEM_DESC_A,
       CASE j.type WHEN 1 THEN e.DEPT_NAME_A END AS "emp_dept",
       CASE j.type WHEN 1 THEN a.DEPT_ID     END AS DEPT_ID,
       CASE j.type WHEN 1 THEN b.DEPT_NAME_A END AS "clear_depts", 
       CASE j.type WHEN 1 THEN c.emp_name_a  END AS emp_name_a,
       CASE j.type WHEN 1 THEN a.APPROVE_HOD END AS APPROVE_HOD,
       CASE j.type WHEN 1 THEN a.HOD_ID      END AS HOD_ID,
       CASE j.type WHEN 2 THEN c.emp_name_a  END AS "Head Name",
       CASE j.type WHEN 1 THEN a.CUST_ID     END AS CUST_ID,
       CASE j.type WHEN 3 THEN c.emp_name_a  END AS "cust name"
FROM   CLEARANCE_ORDERS_CUST a
       INNER JOIN CLEARANCE_DEPARTMENTS b
       ON (a.DEPT_ID = b.DEPT_ID)
       INNER JOIN clearance_orders d
       ON (d.ORDER_NO = a.ORDERS_ID)
       CROSS JOIN LATERAL (
         SELECT 1 AS type, d.EMP_NO AS id FROM DUAL UNION ALL
         SELECT 2, a.hod_id FROM DUAL UNION ALL
         SELECT 3, a.CUST_ID FROM DUAL
       ) j
       INNER JOIN clearance_emp_info c
       ON (c.emp_id = j.id)
       INNER JOIN clearance_departments e
       ON (e.DEPT_ID = c.DEPT_ID)
       INNER JOIN sys_codes f
       ON (f.ITEM_NO = c.NATIONALITY)
WHERE  a.orders_id = 81
AND    f.TABLE_NO = 108
AND    f.HOSPITAL_NO = 720022