troubleshooting Question

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

Avatar of jeffbarbacpa
jeffbarbacpa asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SSRS
1 Comment1 Solution590 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros