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?
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 < ?";
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.