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, they can be associated with that Loan as multiple roles, so we only want to have the Loan amount counted once, 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,

But this won’t work, because the Loan Amount may not be unique across Loans!

I can do this:

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:

but that gives me a list like this:

• 120225 100,000
• 120226 200,00

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:

but couldn’t get that to work, and indeed it looks like that syntax is not supported on 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:

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!