我有三张table。contr\u id中前两个表之间以及第二个表和第三个表之间的连接是enc\u rcpt\u id。
合同

+----+----------+------+-----------+
| 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       |
+----+----------+-------------+-----------+----------+

附件rcpt

+----+-------------+--------------+
| ID | ENC_RCPT_ID | ENC_RCPT_AMT |
+----+-------------+--------------+
| 1  | 1           | 5            |
+----+-------------+--------------+
| 2  | 1           | 15           |
+----+-------------+--------------+
| 3  | 2           | 5            |
+----+-------------+--------------+
| 4  | 2           | 10           |
+----+-------------+--------------+
| 5  | 2           | 15           |
+----+-------------+--------------+

我想显示如下输出：

+----------+------+-----------+----------+--------------+
| CONTR_ID | CURR | TOT_CONTR | RCPT_AMT | ENC_RCPT_AMT |
+----------+------+-----------+----------+--------------+
| 1        | USD  | 100       | 100      | 50           |
+----------+------+-----------+----------+--------------+
| 2        | USD  | 80        | 80       | 0            |
+----------+------+-----------+----------+--------------+

我写了下面的select查询，但它没有给出正确的结果。

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