Re: SQL with several tables

Adam,

How about use a CTE or two?

Something like;

With T1 as
( Select Job_code, count(*) as Planed_num_recs, Sum(hours_Field) as
Planed_ttl_Hours, Sum(amount_Field) as Planed_Ttl_Cost

From Labor_Planed

Group by Job_code
order by job_code ),

T2 as
(Select Job_code, Count(*) as Exec_Num_Recs, Sum(hours_Field) as
Exec_ttl_hours, Sum(amount_field) as Exec_Ttl_cost
from Labor_executed_File
group by Job_code
order by job_code)

Select *
from Master_job a join T1 b on a.job_code = b.job_code
join T2 c on a.job_code = c.job_code
where (your selection goes here based on date or time or other specifics)

HTH

Jim

On Thu, May 14, 2009 at 9:50 AM, Adam West <adamster@xxxxxxxxx> wrote:

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 ?