9 Replies Latest reply on Aug 1, 2016 5:45 AM by tkessler45

    Scope of column definitions in ODBC SQL queries to FileMaker?

    tkessler45

      I have two related tables, one for individual "session" records and another to hold additional records for each session. I am accessing this DB through ODBC, and am trying to write a query that will join information from the two tables, but then also give me aggregate info about the second table. Specifically, I am looking for counts of the additional records for each session.

       

      My SQL query is this:

       

      SELECT

      S.ID AS "Session ID",

      S.Name AS "Name",

      A.Attr AS "Related attribute",

      (SELECT count(*) FROM "additional records" AS x WHERE x.SID=S.ID) as "CNT"

      FROM "sessions" AS S

      JOIN "additional records" AS A ON S.ID=A.SID

      ORDER BY "Name", "CNT" DESC

       

      The problem is I get the error "[FileMaker][FileMaker] Query error" with no explanation. The query works in MySQL and Sqlite, and it appears that if I change "S.ID" to a static numerical value of a valid ID, then the query works. However, this prevents me from using a variable for the ID and therefore not getting the desired counts from the subquery.

       

      Since this only happens in FileMaker, I'm curious if there is a way to perform the above, where "S.ID" in the subquery is a value pulled from the parent select statement?