jbrown

SQL: Multiple-Table Joins

Discussion created by jbrown Expert on Apr 22, 2013
Latest reply on Apr 23, 2013 by 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

Outcomes