Table Join

How can I join gibbonPerson and gibbonCourse table for a query?


  • Hi cmb,

    They're joined through the gibbonCourseClassPerson table, which holds the details of which students and teachers are enroled in each class. Here's an example query "Course/Class Selections in Current Year" which demonstrates the table joins:
    SELECT preferredName, surname, gibbonCourse.nameShort AS course, gibbonCourseClass.nameShort AS class 
    FROM gibbonPerson 
    JOIN gibbonCourseClassPerson 
    ON (gibbonCourseClassPerson.gibbonPersonID=gibbonPerson.gibbonPersonID) 
    JOIN gibbonCourseClass 
    ON (gibbonCourseClassPerson.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID) 
    JOIN gibbonCourse 
    ON (gibbonCourseClass.gibbonCourseID=gibbonCourse.gibbonCourseID) 
    WHERE status='Full' AND role='Student' AND gibbonCourse.gibbonSchoolYearID=(SELECT gibbonSchoolYearID FROM gibbonSchoolYear WHERE status='Current') 
    ORDER BY course, class, surname, preferredName ;
  • Thanks Sandra, great. I will explore now.
Sign In or Register to comment.