Some further testing shows that the join statement above works when at least one of the two tables is in the DB that the script is defined in. However, the join statement does not work when both tables are in an external FMDB. Is this a feature? A bug?
As long as you have a Tutorial: What are Table Occurrences? defined in your current file that references the table in the external database and your SQL expression refers to the table occurrence name in the local file, not the table name in the external file (they may not always be exactly the same), it shouldn't matter if the table is from the local file or an externally sourced file.
After more testing, I seem to be able to run this statement:
SELECT S.id, R.Student_id FROM Student S JOIN Roster R ON S.id = R.id
SELECT S.id, R.Student_id FROM Student S JOIN Roster R ON S.id = R.Student_id
Is the underscore somehow breaking the syntax? I tried to quote it:
SELECT S.id, R.Student_id FROM Student S JOIN Roster R ON S.id = R.\"Student_id\"
but that still did not work. It seems I wouldn't need to quote the field name since it is valid syntax in the list of fields to select.
Seems to have nothing to do with the JOIN. The following returns what I expect:
SELECT R.Student_id from Roster R
But this statement gives me a "?":
SELECT R.Student_id from Roster R where R.Student_id=1
(There are some records in Roster with Student_id = 1)
What Filemaker data types have been specified in Manage | Database for the id and Student_id fields? Could one be of type number and the other of type text?
Thank you. That was exactly it. Frustrating when a little setting like that breaks things in unexpected ways.