Self Join - What am I missing? 1

[mwolf00]

I'm going cross-eyed here... I have a query that is giving me the results I want - the total of all of the categories listed by bill number and month

SELECT 
	b.i_cbr_no
	, b.q_processing_month	
	, b.t_doc_no
	, sum(bt.a_bill)
FROM oaBill.t_oa_billed b 
JOIN oaBill.t_bill_tran bt 
        ON b.i_oa_no = bt.i_oa_no 
        AND b.q_processing_month = bt.q_processing_month
WHERE b.c_region = '11'
AND b.c_ab_code = '9799'
AND b.q_processing_month > 200509
GROUP BY b.i_cbr_no
	, b.q_processing_month
	, b.t_doc_no
ORDER BY b.i_cbr_no, b.q_processing_month

However, when I try to add a self join to get the subtotal of a certain group of billing catagories, the results are incorrect

SELECT 
	b.i_cbr_no
	, b.q_processing_month	
	, b.t_doc_no
	, sum(bt.a_bill)
	[red], sum(bt2.a_bill)[/red]
FROM oaBill.t_oa_billed b 
JOIN oaBill.t_bill_tran bt 
        ON b.i_oa_no = bt.i_oa_no 
        AND b.q_processing_month = bt.q_processing_month
[red]LEFT JOIN oaBill.t_bill_tran bt2 
        ON b.i_oa_no = bt2.i_oa_no 
        AND b.q_processing_month = bt2.q_processing_month 
        AND bt2.c_rectype in('190','200','210','220','230')[/red]
WHERE b.c_region = '11'
AND b.c_ab_code = '9799'
AND b.q_processing_month > 200509
GROUP BY b.i_cbr_no
	, b.q_processing_month
	, b.t_doc_no
ORDER BY b.i_cbr_no, b.q_processing_month

All of the original data is correct, but the new column total is inaccurate. For instance if there is a $8000 credit in January 2006, it will be listed as a $32,000 credit. Is it somehow joining 4 times?

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[Dagon]

It is multiplying the result by 4 because the join between t_oa_billed and t_bill_tran is not unique. For each row in t_oa_billed, it is finding 4 or so matching rows in t_bill_tran. These are then being included in the sum, giving you the wrong results.

You need to make sure the cardinality of the joins is 1-1. For example, you could recode it as:

SELECT 
    b.i_cbr_no
    , b.q_processing_month    
    , b.t_doc_no
    , sum(bt.a_bill)
    , sum(bt2.a_bill)
FROM oaBill.t_oa_billed b 
JOIN oaBill.t_bill_tran bt 
        ON b.i_oa_no = bt.i_oa_no 
        AND b.q_processing_month = bt.q_processing_month
LEFT JOIN (select i_oa_no, 
                  q_processing_month, 
                  sum(bill) as a_bill
            from oaBill.t_bill_tran
            WHERE c_rectype in('190','200','210','220','230')
            group by i_oa_no,
                     q_processing_month) bt2 
        ON b.i_oa_no = bt2.i_oa_no 
        AND b.q_processing_month = bt2.q_processing_month 
where b.c_region = '11'
AND b.c_ab_code = '9799'
AND b.q_processing_month > 200509
GROUP BY b.i_cbr_no
    , b.q_processing_month
    , b.t_doc_no
ORDER BY b.i_cbr_no, b.q_processing_month

 

[mwolf00]

Thanks for the help Dagon, but that isn't working either. I've updated your derived table to

  select t.i_oa_no,
      t.q_processing_month,
      sum(a_bill) as a_bill
  from oaBill.t_bill_tran t
  JOIN oaBill.t_oa_billed b ON t.i_oa_no = b.i_oa_no and t.q_processing_month = b.q_processing_month
  WHERE c_rectype in('190','200','210','220','230')
  and t.q_processing_month > 200509
  and b.c_region = '11'
  group by t.i_oa_no, t.q_processing_month

which gives me the exact number I want when run separately. However, when I join to it, I'm still get a multiplied result...

But wait! I've removed the sum() from around bt2.a_bill and now I'm getting the results that I need. I guess that since I got the sum in the derived table, I don't need it any more in the main query. I'd still like to know how it's getting multiplied...

Thanks again!

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[Dagon]

I should have removed the sum from the main query. It's getting multiplied up because the inline view is in a one-to-one correspondance with t_oa_billed whereas your main query is at the cardinality of t_bill_tran. You are therefore getting the sum from the inline view multiplied up by the number of matching rows in t_bill_tran.