>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!
On bad student solution, remove group by and sum(),
then show they the resultset ...
you can discard every student that don't understand it