Query - Student List by Year Group

Administration
1

Has anyone written the SQL to run a query to find student name (surname & preferred) plus year group (not form group)?

2

SELECT surname, preferredName, gibbonYearGroup.nameShort AS yearGroup,  status FROM gibbonPerson, gibbonStudentEnrolment, gibbonYearGroup, gibbonRollGroup WHERE (gibbonPerson.gibbonPersonID=gibbonStudentEnrolment.gibbonPersonID) AND (gibbonStudentEnrolment.gibbonYearGroupID=gibbonYearGroup.gibbonYearGroupID) AND (gibbonStudentEnrolment.gibbonRollGroupID=gibbonRollGroup.gibbonRollGroupID) AND gibbonRollGroup.gibbonSchoolYearID=‘21’ ORDER BY yearGroup,surname, preferredName


replace the red text with the current year id that can be found by looking at the url when you go to school admin and manage your past, current and upcoming school years.

/index.php?q=/modules/School%20Admin/schoolYear_manage.php

If you click edit on the current year the url will have the number you need.

my url for the current year is 
schoolYear_manage_edit.php&gibbonSchoolYearID=021

I got the query by looking at the page StudentEnrolmentManage.
Hope this helps
G

3

SELECT surname, preferredName, gibbonYearGroup.nameShort AS yearGroup,  status FROM gibbonPerson, gibbonStudentEnrolment, gibbonYearGroup, gibbonRollGroup WHERE (gibbonPerson.gibbonPersonID=gibbonStudentEnrolment.gibbonPersonID) AND (gibbonStudentEnrolment.gibbonYearGroupID=gibbonYearGroup.gibbonYearGroupID) AND (gibbonStudentEnrolment.gibbonRollGroupID=gibbonRollGroup.gibbonRollGroupID) AND gibbonRollGroup.gibbonSchoolYearID=‘21’ ORDER BY yearGroup,surname, preferredName


replace the red text with the current year id that can be found by looking at the url when you go to school admin and manage your past, current and upcoming school years.

/index.php?q=/modules/School%20Admin/schoolYear_manage.php

If you click edit on the current year the url will have the number you need.

my url for the current year is 
schoolYear_manage_edit.php&gibbonSchoolYearID=021

I got the query by looking at the page StudentEnrolmentManage.
Hope this helps
G

4

George, thanks for this : ) Using a subquery, we can replace the red 21 with the following to have the year auto selected to the current year:

(SELECT gibbonSchoolYearID FROM gibbonSchoolYear WHERE status=‘Current’)

Adam,

If the query does what you need, you can save it as a query for your school, or for you as an individual, within Query Builder. If it does not do what you want, let us know and we can amend it.

Ross


5

George, thanks for this : ) Using a subquery, we can replace the red 21 with the following to have the year auto selected to the current year:

(SELECT gibbonSchoolYearID FROM gibbonSchoolYear WHERE status=‘Current’)

The query does include all students, even those whose status is not ‘Full’, so perhaps the one below might be better suited:

SELECT surname, preferredName, username, dob, nameShort AS yearGroup
FROM gibbonPerson
JOIN gibbonStudentEnrolment ON ( gibbonStudentEnrolment.gibbonPersonID = gibbonPerson.gibbonPersonID )
JOIN gibbonYearGroup ON ( gibbonStudentEnrolment.gibbonYearGroupID = gibbonYearGroup.gibbonYearGroupID )
WHERE STATUS = ‘Full’
AND gibbonStudentEnrolment.gibbonSchoolYearID = (
SELECT gibbonSchoolYearID
FROM gibbonSchoolYear
WHERE STATUS = ‘Current’ )
ORDER BY yeargroup, surname, preferredName

Thanks,

Ross

6

Thank you sirs.