I have two tables, Student and Roster, where the Roster table has a Student_id field. The tables are populated such that there are records in Roster where Student_id has the same value as a record in Student.
I am attempting to join the two tables using the ExecuteSQL function. I am able to do a Cartesian join with the following SQL statement:
SELECT S.id, R.Student_id FROM Student S, Roster R
However, when I attempt to perform a JOIN, LEFT OUTER JOIN, or simply add a WHERE clause to the statement above, I get a "?" result. Here are the SQL statements I have attempted:
SELECT S.id, R.Student_id FROM Student S JOIN Roster R ON S.id = R.Student_id SELECT S.id, R.Student_id FROM Student S, Roster R WHERE S.id = R.Student_id SELECT S.id, R.Student_id FROM Student S LEFT OUTER JOIN Roster R WHERE S.id = R.Student_id
Am I missing something in my SQL syntax?
I am running FM Pro 13.
Note - I know this can easily be done using a portal, but I am really attempting to perform a much more complicated query where I would like to join 6 tables with various WHERE clauses.