Useful Joins on PS Tables

Students table to other tables

Join othertable on othertable.studentid = students.id

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


Join othertable on othertable.studentsdcid = students.dcid

- Most of the newer tables and any student extended tables you create will have a studentsdcid field rather than a studentid field, so join the table to the dcid field in the students table


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 schools.school_number = students.schoolid

- Useful when you need to pull the school name or abbreviation into a report.  Note that the field to join to in the schools table is the school_number field and not the id field.


CC table to other tables

Join courses on courses.course_number = cc.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(courses.course_number) = upper(cc.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 schools.school_number = cc.schoolid

- Main thing is to join to the school_number field of the schools table and not the id one.


Join teachers on teachers.id = cc.teacherid

- Legacy way of pulling teacher information.  Unfortunately it can give bad data and you're better off using the following instead:


Join schoolstaff on schoolstaff.id = cc.teacherid
Join users on users.dcid = schoolstaff.users_dcid

- Modern way of pulling teacher information from either the schoolstaff table or users table.  The join to the users table is necessary to pull data such as the teacher name.



  Back to the List of Data Mining Articles

Terms of Use

The downloads on this site are standalone customizations and integration into other customization downloads is prohibited.  All written material on this site was created by Matt Freund.  Use on handouts or on other web sites is prohibited without his written consent.