3 Tables, left outer join, SUM

Question

I'm having a problem with my LEFT OUTER JOIN on 3 tables, with a SUM clause. This is my code:

SELECT r.ret_desc, vmp.vlt_id,  
SUM((vmp.cash_in + vmp.per_cash_in_sec)
  + (CASE WHEN vmp.acc_id = t.db_id AND t.txn_type_id = 1 THEN t.amount ELSE 0 END)
  - (CASE WHEN vmp.acc_id = t.cr_id AND t.txn_type_id = 1 THEN t.amount ELSE 0 END)) AS in_adj, 

FROM ret AS r
LEFT OUTER JOIN vmp ON r.acc_id = vmp.acc_id
LEFT OUTER JOIN txn AS t ON (r.acc_id = t.credit_acc_id OR r.acc_id = t.debit_acc_id) 

WHERE t.txn_type_id IN (1,2,4,5)
GROUP BY r.ret_desc, vmp.vlt_id
;

The problem is that there are multiple VMP records and the SUM field gets multiplied by the COUNT of the VMP records. Ex: If the SUM field should equal $100 and there are 50 VMP records, then the SUM becomes $5000, when it should just be $100. Does anyone know what is wrong with this statement?

Answer 1

I'm going to make some guesses about your data design. Please update the question with more detail if these are not accurate.

(1) RET table has ACC_ID as its primary key
(2) VMP table has (ACC_ID, VLT_ID) as its primary key -- so when you say "there are 50 VMP records" you mean 50 records with the same ACC_ID but different VLT_IDs.

Based on these assumptions, I would guess your problem is that you are not tying individual transactions to a specific VLT_ID.

Descriptions of the table layouts and some sample data would help.

Answer 2

You should be aware that a JOIN produces a set of all combinations matching the join clause.

Therefore, if you have multiple txn and vmp records for a single ret record, you get count(txn) * count(vmp) records per ret, thus creating a sum of count(vmp) * txn.amount and count(txn) * vmp.amount.

I think the best solution is to outer join with 2 subselects, or to create views on vmp and txn that group and sum per ret, and outer join them.

Answer 3

Since all the fields you use for creating the sunm come from tables wiht the potential for mulitple records, of course they all add them in.

There are several issues with what you are doing, but first if I were you I would take the sum out and see what tthe calculation is before you sum it up. INfact I would do something like :

SELECT r.ret_desc, vmp.vlt_id,  
(vmp.cash_in + vmp.per_cash_in_sec)  
 + (CASE WHEN vmp.acc_id = t.db_id AND t.txn_type_id = 1 THEN t.amount ELSE 0 END) 
 - (CASE WHEN vmp.acc_id = t.cr_id AND t.txn_type_id = 1 THEN t.amount ELSE 0 END),
vmp.cash_in,vmp.per_cash_in_sec, vmp.per_cash_in_sec,  t.db_id,t.cr_id,         t.txn_type_id ,t.amount
FROM ret AS r
LEFT OUTER JOIN vmp ON r.acc_id = vmp.acc_id
LEFT OUTER JOIN txn AS t ON (r.acc_id = t.credit_acc_id OR r.acc_id = t.debit_acc_id) 
WHERE t.txn_type_id IN (1,2,4,5)

GROUP BY r.ret_desc, vmp.vlt_id

Second why are you doing left joins? txn is definitely not a left join becaseu you have used it in the wher clause which effectively converts its to an inner join, so if you need it to be a left join, you need to move that where clause to the on clause instead.