1 Reply Latest reply on Jul 5, 2012 6:43 AM by greglane

    ExecuteSQL GROUP BY returning additional fields

    user5859

      When using the executeSQL function with a GROUP BY I'm unable to return additional fields in Filemaker versions 11 and 12, as Filemaker has made the GROUP BY a lot stricter. Because Filemaker has made the GROUP BY syntax stricter there is no way to return additional fields unless you use a subquery in the FROM clause. An example of the work around taken from stack overflow below.

       

      select e.empID, fname, lname, title, dept, projectIDCount
      from
      (
      select empID, count(projectID) as projectIDCount
      from employees E left join projects P on E.empID = P.projLeader
      group by empID
      ) idList
      inner join employees e on idList.empID = e.empID

       

      After some unsuccessful experimentation it would seem that Filemaker's SQL does not support subqueries in the FROM clause.

      http://forums.filemaker.com/posts/43d414725b - "The current SQL driver does not support subqueries in the FROM clause." 10/5/2012

       

      GROUP BY worked flawlessly prior to version 11. (not including subquery)

       

      Questions

      ========

      1. Does this mean the SQL engine will be updated at a future time? We are already two Filemaker versions into a new SQL engine.

      2. Is there another way to return additional fields when doing a GROUP BY without writing a lot of convoluted code?

        • 1. Re: ExecuteSQL GROUP BY returning additional fields
          greglane

          Subqueries can be used in the column list. To get a count of related records from another table, use a correlated subquery. Here's an example that shows a list of base tables in the current file along with a count of fields in each table. Note, the alias from the outer query must be referenced in the WHERE clause of the inner query to make the magic happen.

           

          SELECT DISTINCT basetablename,

              (SELECT COUNT(fieldname)

              FROM filemaker_fields ff

              WHERE ff.tablename = ft.tablename)

          FROM filemaker_tables ft

           

          user5859 wrote:

           

          Is there another way to return additional fields when doing a GROUP BY without writing a lot of convoluted code?