Link to home
Start Free TrialLog in
Avatar of jeffbarbacpa
jeffbarbacpa

asked on

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

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
ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial