6 Replies Latest reply on Mar 3, 2014 2:57 PM by IsaacLeinweber

    ExecuteSQL Confusion

    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. 

        • 1. Re: ExecuteSQL Confusion
          IsaacLeinweber

               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?

          • 2. Re: ExecuteSQL Confusion
            philmodjunk

                 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.

            • 3. Re: ExecuteSQL Confusion
              IsaacLeinweber

                   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

                   but not:

                   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.

              • 4. Re: ExecuteSQL Confusion
                IsaacLeinweber

                     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)

                • 5. Re: ExecuteSQL Confusion
                  philmodjunk

                       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?

                  • 6. Re: ExecuteSQL Confusion
                    IsaacLeinweber

                         Thank you.  That was exactly it.  Frustrating when a little setting like that breaks things in unexpected ways.