I have 3 tables. The join between first two tables in CONTR_ID and between second and third table is ENC_RCPT_ID.
CONTR
+----+----------+------+-----------+ | ID | CONTR_ID | CURR | TOT_CONTR | +----+----------+------+-----------+ | 1 | 1 | USD | 100 | +----+----------+------+-----------+ | 2 | 2 | USD | 80 | +----+----------+------+-----------+
INS_RCPT
+----+----------+-------------+-----------+----------+ | ID | CONTR_ID | ENC_RCPT_ID | RCPT_CURR | RCPT_AMT | +----+----------+-------------+-----------+----------+ | 1 | 1 | 1 | USD | 20 | +----+----------+-------------+-----------+----------+ | 2 | 1 | 2 | USD | 30 | +----+----------+-------------+-----------+----------+ | 3 | 1 | 3 | USD | 50 | +----+----------+-------------+-----------+----------+ | 4 | 2 | 4 | USD | 60 | +----+----------+-------------+-----------+----------+ | 5 | 2 | 5 | USD | 20 | +----+----------+-------------+-----------+----------+
ENC_RCPT
+----+-------------+--------------+ | ID | ENC_RCPT_ID | ENC_RCPT_AMT | +----+-------------+--------------+ | 1 | 1 | 5 | +----+-------------+--------------+ | 2 | 1 | 15 | +----+-------------+--------------+ | 3 | 2 | 5 | +----+-------------+--------------+ | 4 | 2 | 10 | +----+-------------+--------------+ | 5 | 2 | 15 | +----+-------------+--------------+
I want to show output as below:
+----------+------+-----------+----------+--------------+ | CONTR_ID | CURR | TOT_CONTR | RCPT_AMT | ENC_RCPT_AMT | +----------+------+-----------+----------+--------------+ | 1 | USD | 100 | 100 | 50 | +----------+------+-----------+----------+--------------+ | 2 | USD | 80 | 80 | 0 | +----------+------+-----------+----------+--------------+
I have written below select query but it does not give correct result.
SELECT C.contr_id, C.curr, C.tot_contr, IR.rcpt_amt, ER.enc_rcpt_amt
FROM @CONTR AS C
LEFT OUTER JOIN (SELECT contr_id,
enc_rcpt_id,
Sum(rcpt_amt) AS RCPT_AMT
FROM @INS_RCPT
GROUP BY contr_id,
enc_rcpt_id) AS IR
ON IR.contr_id = C.contr_id
LEFT OUTER JOIN (SELECT ER1.enc_rcpt_id,
Sum(ER1.enc_rcpt_amt) AS ENC_RCPT_AMT
FROM @ENC_RCPT AS ER1
INNER JOIN @INS_RCPT AS IR1
ON IR1.enc_rcpt_id = ER1.enc_rcpt_id
GROUP BY ER1.enc_rcpt_id) AS ER
ON ER.enc_rcpt_id = IR.enc_rcpt_id
Advertisement
Answer
You have to force everything to be at the same grain, so you have to roll it all the way up.
SELECT C.contr_id, C.curr, C.tot_contr, r.rcpt_amt, r.enc_rcpt_amt
from
contr c
left outer join (select
contr_id,
sum(rcpt_amt) as rcpt_amt,
sum(enc_rcpt_amt) as enc_rcpt_amt
from
(SELECT contr_id,
enc_rcpt_id,
Sum(rcpt_amt) AS RCPT_AMT
FROM INS_RCPT
GROUP BY contr_id,
enc_rcpt_id) AS IR
LEFT OUTER JOIN (SELECT ER1.enc_rcpt_id,
Sum(ER1.enc_rcpt_amt) AS ENC_RCPT_AMT
FROM ENC_RCPT AS ER1
INNER JOIN INS_RCPT AS IR1
ON IR1.enc_rcpt_id = ER1.enc_rcpt_id
GROUP BY ER1.enc_rcpt_id) AS ER
ON ER.enc_rcpt_id = IR.enc_rcpt_id
group by
contr_id) r
on c.contr_id = r.contr_id