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:
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?