My SQL Database Query

Developers
1

I’ve been tasked with compiling a list of students who received grades equal to or below 60%. While my query successfully retrieved those students, I’m now seeking to include the corresponding course names. However, I lack familiarity with the structure of the Gibbon database. Could you provide guidance on how to incorporate this information?

SELECT rv.gibbonPersonIDStudent, OfficialName, rv.value, rc.*
FROM gibbonReportingValue rv
INNER JOIN gibbonReportingCycle rc ON rv.gibbonReportingCycleID = rc.gibbonReportingCycleID
INNER JOIN gibbonPerson gp ON rv.gibbonPersonIDStudent = gp.gibbonPersonID
WHERE rv.value <= 60
AND rc.dateStart >= '2023-09-03'
AND rc.dateEnd <= '2024-04-20'

GibbonQ
GibbonQ1114×381 13.9 KB

2

Hi @sabercoder You’re off to a great start, and this is one of the more complicated areas for querying, because there’s lots of relational data to connect. Here’s an example of a query that includes course information. Note that the gibbonReportingCriteria table is part of the join, as this will let you filter specific types of criteria, as some may include grades, some comments, etc. In this case, it’s filtering criteria with the name “Term Percent”, be sure to update this to match your own criteria name.

ELECT gibbonReportingCycle.nameShort as `Report Name`, 
    gibbonPerson.surname as `Lastname`, 
    gibbonPerson.firstName as `First Name`, 
    gibbonPerson.preferredName as `Preferred Name`, 
    gibbonYearGroup.nameShort as `Year Group`, 
    gibbonFormGroup.nameShort as `Form Group`, 
    CONCAT(gibbonCourse.nameShort, ".", gibbonCourseClass.nameShort) as `Course Class`,
    CONCAT(teacher.preferredName, ' ', teacher.surname) AS `Course Teacher`, 
    gibbonReportingValue.value as `Grade`, 
    gibbonReportingCriteria.name AS `Grade Type` 
FROM gibbonReportingValue 
    JOIN gibbonReportingCriteria ON (gibbonReportingValue.gibbonReportingCriteriaID=gibbonReportingCriteria.gibbonReportingCriteriaID) 
    JOIN gibbonCourseClass ON (gibbonReportingValue.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID) 
    JOIN gibbonCourse ON (gibbonCourse.gibbonCourseID=gibbonCourseClass.gibbonCourseID) 
    JOIN gibbonCourseClassPerson ON (gibbonCourseClass.gibbonCourseClassID=gibbonCourseClassPerson.gibbonCourseClassID AND gibbonCourseClassPerson.role = 'Teacher') 
    JOIN gibbonPerson AS teacher ON (gibbonCourseClassPerson.gibbonPersonID=teacher.gibbonPersonID) 
    JOIN gibbonReportingCycle ON (gibbonReportingCycle.gibbonReportingCycleID=gibbonReportingValue.gibbonReportingCycleID) 
    JOIN gibbonStudentEnrolment ON (gibbonStudentEnrolment.gibbonPersonID=gibbonReportingValue.gibbonPersonIDStudent) 
    JOIN gibbonFormGroup ON (gibbonFormGroup.gibbonFormGroupID=gibbonStudentEnrolment.gibbonFormGroupID) 
    JOIN gibbonYearGroup ON (gibbonYearGroup.gibbonYearGroupID=gibbonStudentEnrolment.gibbonYearGroupID) 
    JOIN gibbonPerson ON (gibbonPerson.gibbonPersonID=gibbonReportingValue.gibbonPersonIDStudent) 
WHERE gibbonStudentEnrolment.gibbonSchoolYearID=gibbonReportingCycle.gibbonSchoolYearID 
    AND gibbonReportingCycle.dateStart >= '2023-09-03'
    AND gibbonReportingCycle.dateEnd <= '2024-04-20'
    AND  gibbonReportingCriteria.name = 'Term Percent'
    
GROUP BY gibbonPerson.gibbonPersonID, gibbonCourseClass.gibbonCourseClassID 
HAVING (`Grade` > 0 AND `Grade` <= 60.0) 
ORDER BY gibbonYearGroup.sequenceNumber, gibbonFormGroup.nameShort, gibbonPerson.surname, gibbonPerson.firstName, gibbonReportingCriteria.sequenceNumber;
1 Like