Pervasive SQL syntax
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.
How can I get this to return 10,800?
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"
How can I get this to return 10,800?
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
This lead me to the answer... thanks.
The answer I provided does exactly what Kdo recommends. Did you try my solution?
ASKER
Did not... I desired to just eliminate the join.
SQL
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
SELECT Sum(U.Amount) as AmountOwed
FROM (
SELECT TableA.Mortage as Amount
FROM TableA
UNION ALL
SELECT TableB.Amount
FROM TableB
) as U