RE: SQL with several tables

[<lgoodbar@xxxxxxxxxxxxxx>]

If you can use SQL, this might work:

Select mj.job, sum(coalesce(lp.hours,0)), sum(coalesce(le.hours,0))
> From master_job mj
Left outer join labor_plan lp on (mj.job=lp.job)
Left outer join labor_exec le on (mj.job=le.job) 

If you get duplicates or off-totals from this, try:
 
With letemp as (select job, sum(hours) from labor_exec),
     Lptemp as (select job, sum(hours) from labor_plan)
Select mj.job, sum(coalesce(lp.hours,0)), sum(coalesce(le.hours,0))
> From master_job mj
Left outer join lptemp lp on (mj.job=lp.job)
Left outer join letemp le on (mj.job=le.job)

HTH,
Loyd

-----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 ?