AnsweredAssumed Answered

Scope of column definitions in ODBC SQL queries to FileMaker?

Question asked by tkessler45 on Jul 31, 2016
Latest reply on Aug 1, 2016 by 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?

Outcomes