6 Replies Latest reply on Apr 23, 2013 6:35 AM by jbrown

    SQL: Multiple-Table Joins

    jbrown

      Hey all,

      I'm grappling with understanding how to work with SQL in filemaker to grab data from multiple tables, not just from one table to another.

      I assume SQL is the replacement for starting one table (Students) and joining it to another table (Enrollment). The enrollment table is joined to another table (Courses). Instead of using the graph, I can duplicate this in SQL, right?

       

      Here's a sample of what i needed:

      I need to pull the following information:

      1. Student ID (Students table)

      2. FirstLast Name (StudentsTable)

      3. Semester 1 grade (HS_GradeSForSemester)

      4. Class Name (Courses Table (HS_Schedule_Courses)

      5. Quarter 3 Grade (HS_Grades)

       

       

      So I put together this SQL statement and it seemed to work. I'm sure sure sure there's people out there that can reduce the clutter in here (please let me know how to declutter it) but it seems as if I'm 'getting' how to use multiple joins: Each join in SQL is like the Filemaker Relationship graph and the line between two tables, I am hopefully understanding that if I can create the relationship in the graph interface, I can create it here in SQL. Am I anywhere correct or getting the right Idea?

      ExecuteSQL("

      SELECT k.Apk_StudentID, k.FirstName, k.LastName, k.GradeNo, g.ClassID, s.CourseName, g.Semester1, e.pk_ClassID, c.CourseName, q.Percentage

       

      FROM A_KIPPsters k

       

      JOIN HS_GradeSForSemester g

      ON k.Apk_StudentID = g.StudentID

       

      JOIN HS_Schedule_Courses s

      ON g.ClassID = s.ClassID

       

      JOIN HS_Enrollment e

      ON e.fk_StudentID = k.Apk_StudentID

       

      JOIN HS_Schedule_Courses c

      ON c.ClassID = e.pk_ClassID

       

      JOIN HS_Grades q

      ON q.StudentID = k.Apk_StudentID

       

      WHERE g.ClassID Like ?

      AND g.Semester1 IS NOT NULL

      AND e.pk_ClassID LIKE ?

      AND q.pk_ClassID LIKE ?

      AND q.QuarterNumber = ?

      AND e.ClassExitDate < ?";

      "";"";

       

      "SCI%"; "SCI%";"SCI%";3;Date(6;15;2013)

      )

       

      I'd love some advice on my success in this and even some tips on how to make this more efficient.

      BTW: It did pull what I needed it to get, and the data seems accurate.

       

      thanks

      jb

        • 1. Re: SQL: Multiple-Table Joins
          LyndsayHowarth

          Hi Jeremy,

           

          Just wondering why? Yes you can do the SQL queries etc... but what motivates you to do it this way instead of using relationships on the FMP graph? Given the right structure in FMP you can pull data from several relationships simultaneously.

           

          - Lyndsay

          • 2. Re: SQL: Multiple-Table Joins
            mbraendle

            Jeremy,

             

            yes, you are correct. You can either create the relations in JOINs or you can just list all tables in the FROM part and place the conditions for the relations that are in the ON parts into the WHERE part.

             

            But with ExecuteSQL() one is not only restricted to the relationship graph. One can create on the fly relations between TOs that don't have relationships between them. This makes the relationship graph less cluttered, because one doesn't have to consider every case to write out the relationship graph just to get data from a distant corner.

             

            Lyndsay,

             

            can't speak for Jeremy, but one of my motivations is to make search interfaces which are much more powerful than what FileMaker offers with the standard "Find in fields which must be on a layout" technique. More Web-like: The user pulls in the fields he requires (and doesn't have to bother from which table they come), specifies the criteria (which he even might look up from what is already around), and may even use non-standard operators which allow fuzzy searching. The SQL engine translates everything in the background to the given DB model.

            • 3. Re: SQL: Multiple-Table Joins
              jbrown

              Lindsay,

              I guess I only have two reasons for doing this:

              1. Its more of a challenge to pull data using ExecuteSQL(). I can certainly do it using the graph, but it makes it more fun to try and get it to work this way.

              2. This is a one-time only data pull for the school leader. It will likely never be needed again. In cases where the data will be used a lot, I would rather pull it using the graph, but it didn't seem worth the trouble. I do have relationships already set up to these tables from the student table, but they are more general ones, showing all the grades or enrollment of a student. I thought it better to not create new relationships and/or mess with the established ones for this one-time data pull.

               

              I'm sort of in love with ExecuteSQL. I use it more than anything else right now to get data, create records, etc.

               

              Martin,

              You made me realize that I have done that before: use multiple FROMs and then set conditions in the WHERE to mimic the relationship. Huh. That seems a quicker route to this.

               

               

              But my structure is correct right?

              • 4. Re: SQL: Multiple-Table Joins
                taylorsharpe

                Structure might be right, bugt are you sure your joins are correct ones for what you want?  Left Join, Right Join, Cross, Outer, etc. I have had probllems with that before in my joins. 

                • 5. Re: SQL: Multiple-Table Joins
                  wimdecorte

                  Lyndsay Howarth wrote:

                   

                   

                   

                  Just wondering why? Yes you can do the SQL queries etc... but what motivates you to do it this way instead of using relationships on the FMP graph? Given the right structure in FMP you can pull data from several relationships simultaneously.

                   

                   

                  Martin already explained why but I want to chime and reinforce the point:  using ExecuteSQL you can dramatically simplify the graph and only create relationships for UI requirements (showing portals / related data )

                  That will make your solution a lot more efficient.

                   

                  There defintely is a performance cliff when you cram JOINs into the ExecuteSQL so some careful testing is needed to see how far you can push it.

                   

                  Everything in FM is context-based.  That has been the fundamental given since forever.  ExecuteSQL allows you to break away from that.  You can be on Table A and get data from Table B without navigating there or using a relationship on the graph.  That is huge.

                  • 6. Re: SQL: Multiple-Table Joins
                    jbrown

                    I especially agree with this when it comes to a one-time data pull. There's no need to complicate the graph when you need data once. It was a bit slow but nothing i couldn't do while watching Hulu.