Why Is The Join Messing Things Up?

[cLFlaVA]

Hi people...

given the following query:

   select sp.sub_id as project_id
        , cd_bu.code_short_descr as bus_unit
        , cd_pg.code_short_descr as program_group
        , pr.program_name as program
        , sp.sub_name as project_name
        , sum( cfb.cost_amount ) as fiscal_budget
        , sum( cob.cost_amount ) as operating_budget
     from sub_project sp join code_decode cd_bu on sp.sub_bu_cd = cd_bu.code and cd_bu.category_id = 2
     join code_decode cd_pg on sp.sub_prg_grp_cd = cd_pg.code and cd_pg.category_id = 19
     join program pr on sp.program_id = pr.program_id
     left join cost cfb on sp.sub_id = cfb.sub_id and cfb.cost_budget_year = 2005 and cfb.cost_type_cd = 67 and cfb.cost_active_ind = 1 and cfb.cost_orig_ind = 0
     left join cost cob on sp.sub_id = cob.sub_id and cob.cost_budget_year = 2005 and cob.cost_type_cd = 67 and cob.cost_active_ind = 1 and cob.cost_orig_ind = 1
    where sp.sub_id in (105)
 group by sp.sub_id
        , cd_bu.code_short_descr
        , cd_pg.code_short_descr
        , pr.program_name
        , sp.sub_name

if i only have one cost table instance in there (cfb, for example), it works fine. however, when I add a second and third, the totals are higher.

what concept am i missing? can i NOT have all that join mumbo jumbo in there?

thanks!

Cory

*cLFlaVA
----------------------------
[tt]I already made like infinity of those at scout camp...[/tt]
beware of active imagination: [URL unfurl="true"]http://www.coryarthus.com/[/url]

BillyRayPreachersSonIsTheLeetestHax0rDude

 

[SQLDenis]

why not this
left join cost cfb on sp.sub_id = cfb.sub_id and cfb.cost_budget_year = 2005 and cfb.cost_type_cd = 67 and cfb.cost_active_ind = 1 and cfb.cost_orig_ind in( 0,1)


instead of

left join cost cfb on sp.sub_id = cfb.sub_id and cfb.cost_budget_year = 2005 and cfb.cost_type_cd = 67 and cfb.cost_active_ind = 1 and cfb.cost_orig_ind = 0
left join cost cob on sp.sub_id = cob.sub_id and cob.cost_budget_year = 2005 and cob.cost_type_cd = 67 and cob.cost_active_ind = 1 and cob.cost_orig_ind = 1


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[cLFlaVA]

... because i'm trying to return operating budget AND fiscal budget - two distinct record types in the same table - differentiated only by that 0 or 1.

*cLFlaVA
----------------------------
[tt]I already made like infinity of those at scout camp...[/tt]
beware of active imagination: [URL unfurl="true"]http://www.coryarthus.com/[/url]

BillyRayPreachersSonIsTheLeetestHax0rDude

 

[cLFlaVA]

i guess i'll just use subqueries:

   select sp.sub_id as project_id
        , cd_bu.code_short_descr as bus_unit
        , cd_pg.code_short_descr as program_group
        , pr.program_name as program
        , sp.sub_name as project_name
        , (select sum( cost_amount ) from cost where sub_id = sp.sub_id and cost_budget_year = 2005 and cost_type_cd = 67 and cost_active_ind = 1 and cost_orig_ind = 0) as fiscal_budget
        , (select sum( cost_amount ) from cost where sub_id = sp.sub_id and cost_budget_year = 2005 and cost_type_cd = 67 and cost_active_ind = 1 and cost_orig_ind = 1) as operating_budget
        , (select sum( cost_amount ) from cost where sub_id = sp.sub_id and cost_actual_year_ended = 2005 and cost_type_cd = 68 and cost_active_ind = 1) actuals
     from sub_project sp join code_decode cd_bu on sp.sub_bu_cd = cd_bu.code and cd_bu.category_id = 2
     join code_decode cd_pg on sp.sub_prg_grp_cd = cd_pg.code and cd_pg.category_id = 19
     join program pr on sp.program_id = pr.program_id
    where sp.sub_id in (105)
 group by sp.sub_id, cd_bu.code_short_descr, cd_pg.code_short_descr, pr.program_name, sp.sub_name
 order by cd_bu.code_short_descr, cd_pg.code_short_descr, pr.program_name, sp.sub_name, operating_budget desc

*cLFlaVA
----------------------------
[tt]I already made like infinity of those at scout camp...[/tt]
beware of active imagination: [URL unfurl="true"]http://www.coryarthus.com/[/url]

BillyRayPreachersSonIsTheLeetestHax0rDude