Casualty of the flu...inability to debug the simplest SQL statements and asking really stupid questions. I'll strip this down to the basics.

I have three tables...

JOBMAST, keyed on JOBNUMBER
MATERIALS, with many records for a JOBNUMBER
LABOR, with many records for a JOBNUMBER

I have one row in JOBMAST, for JOBNUMBER 1
In MATERIALS, I have two rows for JOBNUMBER 1, amounts of 100 and 200
In LABOR, I have two rows for JOBNUMBER 1, amounts of 1000 and 2000

I have one query to sum the amounts in MATERIALS and LABOR...it looks like this...

SELECT JobMast.JobNumber,
SUM(Materials.Amount) AS MaterialAmount,;
SUM(Labor.Amount) AS LaborAmount ;
FROM JobMast
left join Labor ON jobmast.jobnumber = Labor.jobnumber
LEFT JOIN Material on JobMast.JobNumber=Material.JobNumber
group by JobMast.JobNumber

I expect to get back dollar values of 300 and 3000...but I get back 600 and 6000. So the dollars are getting double-counted because of the two rows in the child tables. But how would I code this correctly? If I break it into two queries, it works fine, but I'd prefer one query if possible. Reason for the left join is that some jobs might have labor but not materials, or vice-versa.

I'll gladly trade pride for an answer on this one!

Thanks,
Kevin