Re: Can this query be improved ?

[Rick James]

Currently:

SELECT  M.rec_date, M.dak_type, M.dairy_no,
        M.subject, M.dep_name_from, DP.DEP_NAME as dep_name_to,
        M.entry_u_n, M.entry_date, M.action_taken_1,
        M.action_taken_date_1, M.action_taken_u_n_1,
        M.action_taken_2, M.action_taken_date_2,
        M.action_taken_u_n_2
    FROM  
( SELECT  d.rec_date, dt.dak_type, d.dairy_no,
        d.subject, d.dep_code_from,dep.DEP_NAME as dep_name_from,
        d.dep_code_to,'' as dep_name_to, d.entry_u_n,
        d.entry_date, d.action_taken_1, d.action_taken_date_1,
        d.action_taken_u_n_1, d.action_taken_2,
        d.action_taken_date_2, d.action_taken_u_n_2
    FROM  dailydak d, department dep, dak_types dt
    WHERE  (d.dep_code_from=dep.DEP_CODE)
      and  (d.dak_type_id = dt.type_id) ) M ,
        department DP
    WHERE  M.dep_code_to=DP.DEP_CODE

Would it work to turn it inside out?

SELECT  d.rec_date, dt.dak_type, d.dairy_no,
        d.subject, d.dep_code_from,
        ( SELECT DP.DEP_NAME
               FROM department DP
               WHERE d.dep_code_to = DP.DEP_CODE 
            ) as dep_name_to
        dep.DEP_NAME as dep_name_from,
        d.dep_code_to, '' as dep_name_to, d.entry_u_n,
        d.entry_date, d.action_taken_1, d.action_taken_date_1,
        d.action_taken_u_n_1,  d.action_taken_2,
        d.action_taken_date_2, d.action_taken_u_n_2
    FROM  dailydak d, department dep, dak_types dt
    WHERE  d.dep_code_from = dep.DEP_CODE
      and  d.dak_type_id = dt.type_id

At that point, it looks like the dep_name_to and dep_name_from are essentially lookups on department. Right?

If so, JOINing would probably be best...

SELECT  d.rec_date, dt.dak_type, d.dairy_no,
        d.subject, d.dep_code_from,
        dto.DEP_NAME   as dep_name_to,
        dfrom.DEP_NAME as dep_name_from,
        d.dep_code_to, '' as dep_name_to, d.entry_u_n,
        d.entry_date, d.action_taken_1, d.action_taken_date_1,
        d.action_taken_u_n_1,  d.action_taken_2,
        d.action_taken_date_2, d.action_taken_u_n_2
    FROM  dailydak d,
          department dto,
          department dfrom,
          dak_types dt
    WHERE  d.dep_code_to   = dto.DEP_CODE
      AND  d.dep_code_from = dfrom.DEP_CODE
      and  d.dak_type_id = dt.type_id

Note the two uses of department table.

Sorry if I mangled the code; hope you see how to simplify.