I’ve had some small pre-FileMaker experience with SQL and had it in my head that subqueries work in the FROM clause. They do in MySQL etc, so I was frustrated when I couldn’t get that to work in FileMaker’s SQL as it’s really needed in some cases.
In a current project we have a case where there is a relationship from Contacts to Loans and we wanted to see the total Loan amount from the Contact context. However, Contacts can be associated with a Loan using multiple roles, and we only want to have the Loan amount counted once for the Contact, otherwise the total will seem inflated. To further complicate this, the Portal is filtered, and we want to see the filtering reflected in the total. This, I thought was a job for SQL…
I first thought some kind of DISTINCT statement would allow me to just summarize the amount once per loan, but you can only apply DISTINCT to the field you want Summed. For example,
SELECT SUM ( DISTINCT “Loan Amount” )
But this won’t work, because the Loan Amount may not be unique across Loans!
I can do this:
SELECT DISTINCT “LoanID”, SUM ( “LoanAmount” )
but that gives me the first Loan Number and Amount, not a total I can use. If I add GROUP BY I get a little closer:
SELECT DISTINCT “LoanID”, SUM ( “LoanAmount” ) FROM Loans GROUP BY “LoanID”
but that gives me a list like this:
- 120225 100,000
- 120226 200,000
They’re distinct amounts, but I still need to parse out the second column and sum the amounts. I think “No problem, this is a sub query and I know how to do that.” And try to get this to work:
SELECT SUM ( “Amount” ) FROM ( SELECT DISTINCT “LoanID” As Number, “Loan Amount” As Amount FROM LOANS )
but couldn’t get that to work, and indeed it looks like that syntax is not supported in FM SQL. I came up with a clever tail custom function to total the columns from my GROUP BY query that worked…and began the subtle fear of knowing that someday that 49,999 recursion limit would be hit
After leaving it for a bit I looked at the ODBC guide again and noted that indeed subqueries are supported, but only in the WHERE clause. After letting that sink in I realized that I could easily get the result I wanted from a single query:
SELECT SUM ( “LoanAmount” ) FROM Loans WHERE “LoanID” IN ( SELECT DISTINCT “LoanID” FROM Loans )
and voila! Probably 101 for real SQL folks, but I was pleased and have found references and docs on subqueries in FMSQL scarce. Powerful stuff and no recursion limit!