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!
10 Comments
So glad I revisited this post. I was digging into some sub queries today and it made my head hurt for a few. This totally got me on track! Thank you Jason for your post and all the people who commented. Cheers!
Hey Jason,
Awesome post! Subqueries were definitely something I didn’t know where possible until recently and I’m glad you could bring some light into how awesome they are!
Here’s an example of where I used a subquery to get a weighted average in one step.
The ExecuteSQL is returning something that looks like 5/15+4/15+5/15+1/25 and then the Evaluate function is actually doing the math. The subquery needs the same logic as the main query, so the “?” parameters are duplicated. Saves having to run two queries!
Very cool, so you can nest sub-queries as columns. I certainly didn’t know that, so thanks so much for sharing! Also, very clever building an actual expression as your result! “Hacking” those delimiters is something I need to play around with for sure!
I don’t know jack squat about FM SQL, but am wondering if there is a missing close quote in the final parenthetical.
Hi Bob,
Right, good eye! Actually the starting quote on the sub-query was wrong. I corrected.
Thanks!
Very nice Jason! 🙂
Nice stuff, Jason!
One other nice thing about this technique: In FQL benchmark tests, we found that queries using a subquery in the WHERE clause like this typically run faster than equivalent queries constructed using an INNER JOIN — often *much* faster.
An added bonus: To me at least, your subquery technique also reads better than an equivalent JOIN query, since it pushes complexity to the WHERE clause, and streamlines the main FROM and SELECT clauses. All good…
Hi Brian,
Thanks! although referring to this as “my” technique does make me smile.
I’ve read on various non FM SQL posts that the way WHEREs are handled is more optimized than in the other clauses and that’s why you have a sizable group insisting that Implicit Joins (relationship done in the WHERE clause) are faster than doing the more familiar (to me at least) explicit Joins that are done in the FROM clause. Your benchmarks seem to back this up. Benchmark testing on this stuff both compared to traditional FileMaker and within FQL itself will hopefully become more available. I’ve got it on my list to run some, but need that 8th day in the week!
Jason,
thanks for posting this. There have been many times where I could have used subqueries, and I thought they were not possible. Generally I would resort to separate queries. I wonder if you could have also used a summary field in a filtered portal. In this case, the summary field would be in the loans table, and be a total of the loan amount. Then place the summary field in a hidden portal in the contact context and using the same filtering as the main loans portal.
Hi Darren,
Thanks and glad you liked that post. Right, I’ve used that technique and it’s a great one. That was my first choice here, but I couldn’t get it to work. I’m not sure why that works in some cases and not others, but the relationship is two “jumps” away and that may have something to do with it. Although I am in my infatuation stage with ExecuteSQL, I’ll go with a FileMaker technique if it’s faster!