Useful Joins on PS Tables

Students table to other tables

From Students

Join othertable on students.id = othertable.studentid - the othertable could be cc, attendance, storedgrades, etc.  Any table that has a studentsid field in it can be joined back to the students table.

Join othertable on students.dcid = othertable.studentsdcid - most of the newer tables and any student extended tables you create will have a studentsdcid field rather than a studentid field

Join ~[temp.table.current.selection:students] stusel ON stusel.dcid=s.dcid - join the current selection in PS to the students table, only works in tlist_sql on PS pages or in sqlReports.  See the How to Use the Current Selection in a sqlReports Report for more information on how to use the join in sqlReports.

Join schools on students.schoolid = schools.school_number - useful when you need to pull the school name or abbreviation into a report


CC table to other tables

From CC

Join courses on cc.course_number = courses.course_number - useful when needing to pull course name or other things in the courses table, best practice is to actually use something like upper(cc.course_number) = upper(courses.course_number) to be sure to catch all courses as alphanumeric course numbers may not be the same for the same course for some reason

Join schools on cc.schoolid = schools.school_number

Join sections on cc.sectionid = sections.id

Join teachers on cc.teacherid = teachers.id - legacy way of pulling teacher information

Join schoolstaff on cc.teacherid = schoolstaff.id, followed by join users on users.dcid = schoolstaff.users_dcid - modern way of pulling teacher information from either the schoolstaff table or users table

Join terms on cc.termid = terms.id


Sections table to other tables

Same as above for the CC table - just replace cc with sections, such as Join terms on sections.termid = terms.id

 

 

  Back to the List of Articles