Query optimization for multiple inner joins and sub-query

I need help regarding query optimization of the below query.

SELECT pr.todate , pr.descr, cmp.company_id 
FROM employee AS emp
INNER JOIN company AS cmp ON emp.emp_comp_id = cmp.company_id 
INNER JOIN profile AS pr ON emp.acca_id = pr.profile_id 
INNER JOIN acondition ON as_id = as_ac_id
WHERE as_closed = 0 
  AND (pr.ac_act_id = 20)
  AND (pr.todate = (SELECT MIN(todate) AS Expr1 
                    FROM profile pro 
                    INNER JOIN employee empl ON empl.acca_id = pro.profile_id 
                    JOIN acondition ON as_id = as_ac_id 
                    WHERE (pro.ac_act_id = 20 
                           AND empl.emp_comp_id = cmp.company_id) 
                      AND as_closed = 0)) 

Since there are duplicate joins in the main query and sub query, is there any way to remove those joins in the subquery?

Answer

Since, as you clarified, your sub-query is almost identical to your main query you might be able to use the window function RANK as a filter condition. RANK assigns the same number to ties, meaning if multiple records per company match you will get them all e.g.

SELECT todate, descr, company_id
FROM (
    SELECT pr.todate, pr.descr, cmp.company_id
      , RANK() OVER (PARTITION BY cmp.company_id ORDER BY pr.todate ASC) RankNumber
    FROM employee AS emp
    INNER JOIN company AS cmp ON emp.emp_comp_id = cmp.company_id 
    INNER JOIN profile AS pr ON emp.acca_id = pr.profile_id 
    INNER JOIN acondition ON as_id = as_ac_id
    WHERE as_closed = 0 AND pr.ac_act_id = 20
) X
where RankNumber = 1;