AnsweredAssumed Answered

ExecuteSQL Confusion

Question asked by IsaacLeinweber on Mar 3, 2014
Latest reply on Mar 3, 2014 by IsaacLeinweber

Title

ExecuteSQL Confusion

Post

     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. 

Outcomes