How to avoid using subquery in this situation Access SQL

Question

Hi I have a situation where I need to Join two tables but filter value from a third table. As below:

SELECT a.Key , (SELECT SUM(B.hours) FROM tableB as B 
                 INNER JOIN tableC as C ON B.List=C.List 
                 WHERE C.Status = 'Approved' AND B.Key LIKE A.key) , 
               (SELECT SUM(B.hours) FROM tableB as B 
                 INNER JOIN tableC as C ON B.List=C.List 
                 WHERE C.Status = 'Pending' AND B.Key LIKE A.key) 
FROM tableA as A GROUP BY A.key

is there another way to do this without using correlated subqueries? i'd like to use joins , but i just don't know how to link table C into this picture.

Thanks in advance.

Answer 1

The subquery may not be such a bad idea, but you can do this with conditional aggregation:

SELECT a.Key,
       sum(iif(c.Status = "Approved", B.hours, 0)),
       sum(iif(c.Status = "Pending", B.hours, 0))
FROM tableA as A left join
     (tableB as b inner join
      tableC as c
      on b.list = c.list
     ) 
     on b.key like a.key
GROUP BY A.key;

MS Access has arcane syntax for joins, particularly for multiple joins. I think the above is correct.