RE: SQL with several tables

I would probably use Common Table Expressions to summarize the labor
files, then join the summary data to your job master...

With
lPlan (jobid, jobhours) as
( Select jobid, sum(jobhours)
From laborPlan
Group by jobid ),

lExec (jobid, jobhours) as
( Select jobid, sum(jobhours)
From laborExec
Group by jobid )

Select master_job.*, lPlan.jobhours, lExec.jobhours

From master_job

left outer join
lPlan on (master_job.jobid = lPlan.jobid)
left outer join
lExec on (master_job.jobid = lExec.jobid)


hth,
-Eric DeLong


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Adam West
Sent: Thursday, May 14, 2009 10:51 AM
To: Midrange Systems Technical Discussion
Subject: SQL with several tables

Hi everybody,

I have 3 tables, one with the master_job, other is with Labor planed and
other with labor executed - those labors could have many lines in it.

I need to create a report that shows for each job the total amout of
planed labor and the total amount of executed labor.

Because the labor tables are linked to the master_job, when i create the
report the result is duplicating the lines.

Anyone knows how to extract just the totals for each job with the total
planed and total executed ?