I have been using SQL queries in FM for quite some time. I'm currently moving gradually from using a plugin for purely reading queries to using the ExecuteSQL script step of FM12.
I have been working on a problem with a subquery (nested JOIN) for a number of hours now. I've tried so many ways of performing the query and I just can't work it out. Whenever I reduce the complexity by one level, the queries work just fine, but as soon as I try to do the nested JOIN described below, I can't get it to work. I even wrote my own SQL query testing FM12 database with a few simple tables, which are interconnected in various ways and a layout where I can write the queries and instantaneously get the result. Many things work (e.g. a sequence of JOIN statements which are not nested), but not the one below.
Now the question is whether the problem is me or whether I hit on a FM SQL limitation .
Imagine three tables myA, myB and myC.
I'm trying to carry out a nested subquery of the following kind:
FROM myA A
LEFT JOIN (
SELECT B.id, B.myAid, B.bbb, C.ccc
FROM myB B
LEFT JOIN myC C
ON B.myCid = C.id
ON A.id = D.myAid
As mentioned above, I've tried different approaches and all failed. So, what I'm posting here is just one of dozens of versions.
Has anyone carried out similar queries successfully?
Can you either spot the mistake in my query or confirm that there are certain limitations in the FM12 SQL engine?
I would be grateful for your hints.