We help IT Professionals succeed at work.

SQL Query trying to flatten multiple one to many tables into one table

jeffbarbacpa
jeffbarbacpa used Ask the Experts™
on
Hi all...so, I'm trying to create a flat table with the following:

LOAN NUMBER
CREDITS
SUSPENSE TRANSACTIONS

However, the table with the loan numbers, has a one to many relationship with the CREDITS table LN_HUDARR10 and the SUSPENSE TRANSACTIONS table U_LN_ACCT_OTH_SUSP.  So when I try to do a group and sum, it multiplies what should be the total by the number of records.  How can I eliminate this?  Below is my query.  Thanks!

SELECT     EMPOWER.U_LN_CODES.LNKEY, SUM(EMPOWER.LN_HUDARR10.H201_209_AMT) AS Expr1,
                      SUM(EMPOWER.U_LN_ACCT_OTH_SUSP.OTH_SUSP_AMOUNT) AS Expr2
FROM         EMPOWER.U_LN_CODES LEFT OUTER JOIN
                      EMPOWER.LN_HUDARR10 ON EMPOWER.U_LN_CODES.LNKEY = EMPOWER.LN_HUDARR10.LNKEY LEFT OUTER JOIN
                      EMPOWER.U_LN_ACCT_OTH_SUSP ON EMPOWER.U_LN_CODES.LNKEY = EMPOWER.U_LN_ACCT_OTH_SUSP.LNKEY
WHERE     (EMPOWER.LN_HUDARR10.IDX BETWEEN 5 AND 9) AND (EMPOWER.U_LN_ACCT_OTH_SUSP.OTH_SUSP_TRANSACT = '6') AND
                      (EMPOWER.U_LN_ACCT_OTH_SUSP.OTH_SUSP_TRANS_TYPE = 'A')
GROUP BY EMPOWER.U_LN_CODES.LNKEY
HAVING      (EMPOWER.U_LN_CODES.LNKEY = '2010-1495352' OR
                      EMPOWER.U_LN_CODES.LNKEY = '2010-1558999')
ORDER BY EMPOWER.U_LN_CODES.LNKEY
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
You need to sum() and filter before you join.  Try this:


SELECT Loans.LNKEY
  , credit_sum
  , suspense_sum
FROM
EMPOWER.U_LN_CODES as loan LEFT OUTER JOIN
(Select LNKEY, SUM(H201_209_AMT) as credit_sum
      from EMPOWER.LN_HUDARR10
      where IDX BETWEEN 5 AND 9
      group by LNKEY
) as Credit 
      ON Loan.LNKEY = Credit.LNKEY
      LEFT OUTER JOIN
 (Select LNKEY, sum(OTH_SUSP_AMOUNT) as suspense_sum
     from EMPOWER.U_LN_ACCT_OTH_SUSP
     where OTH_SUSP_TRANSACT = '6'
       and OTH_SUSP_TRANS_TYPE = 'A'
     group by LNKEY) as suspense
          ON loan.LNKEY = suspense.LNKEY

WHERE loan.LNKEY in ( '2010-1495352', '2010-1558999')
ORDER BY loan.LNKEY

Open in new window