Thanks!
Yup, both answers are good. (And you can use a CTE as well).
A few of my students try it with...
select JobMaster.JobNumber, sum(HoursWorked), sum(PurchaseAmount)
from JobMaster
LEFT JOIN JobMaterials on JobMaster.JobMasterPK = JobMaterials.JobMasterPK
LEFT JOIN JobTimeSheets ON JobMaster.JobMasterPk = JobTimeSheets.JobMasterPK
GROUP BY JobMaster.JobNumber
...and either don't realize (or don't understand) why they get double-counting in the result.
I've have different discussion with people on the correlated subquery approach versus the derived table approach. Some people prefer the correlated subquery approach, finding it more readable. By nature, I usually don't think to try correlated subqueries (at least not at first), so I'm just so accustomed to doing it with derived tables (which would explain why I like CTEs so much)
Thanks!