Re: SQL with several tables

[Jim Essinger <dilbernator@xxxxxxxxx>]

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 ?