Solved

Pervasive SQL syntax

Posted on 2014-12-30
5
209 Views
Last Modified: 2014-12-30
Imagine I have Table A with one record.  The "Mortgage" field contains 10,000.

Table B (other loans) has two records (because this one guy has a second and third mortgage). The "Amount" fields contain $300 and $500.

When I use a left outer join to add up the Mortgage and Amount fields I get 20,800.

I assume that this is because the left outer join returns 2 records... 10,000 and 300 as well as 10,000 and 500.

SELECT 
  "ID" AS "ID",
  "strName" AS "Borrower Name",
  SUM(("Mortgage" + IF("Amount" IS NOT NULL,"Amount",0))) AS "Total Owed"

FROM 
  "TableA"
LEFT OUTER JOIN
  "TableB"

ON
  "MLoan" = "OLoan"

GROUP BY 
  "ID", "strName"

ORDER BY
  "ID"

Open in new window


How can I get this to return 10,800?
0
Comment
Question by:classnet
  • 2
  • 2
5 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40524424
YOu could use a union query:

SELECT Sum(U.Amount) as AmountOwed
FROM (
SELECT TableA.Mortage as Amount
FROM TableA
UNION ALL
SELECT TableB.Amount
FROM TableB
) as U
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 40524425
You want to sum the total in table A and add it to the summed total in table B.

No join is necessary.
0
 

Author Closing Comment

by:classnet
ID: 40524495
This lead me to the answer... thanks.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40524614
The answer I provided does exactly what Kdo recommends.  Did you try my solution?
0
 

Author Comment

by:classnet
ID: 40524632
Did not... I desired to just eliminate the join.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now