Useful Database Joins
Reports Table (with reports as r)
| Table Being Joined | Join | Benefit |
| t_schedule_instances (as si) | si.id = r.scheduled_instance | This allows us to join the report to the scheduled instance to track schedule compliance. |
| t_answers (as a) | a.report_id = r.report_id | This allows us to join the information of the reports table to the responses of the report in the answers table. |
| t_units (as u) | u.id = r.subject_unit_id | This allows us to get more information about the unit we have created a report against such as its type. |
| t_unit_info (as ui) | ui.unit = r.subject_unit_id | This allows us to get unit attribute information associated with the unit. |
| t_unit_relation (as ur) | ur.child = r.subject_unit_id | This allows us to build the parent child relationship unit hierarchy from the subject unit of the report. We would then join the units table again to unit info but as ur.parent = u.id |
| t_users (as u) | u.id = r.author_id | This allows us to get more information about the report submitter such as their email and when they signed up. |
| t_team_membership (as tm) | tm.member = r.author_id | This allows us to join the team membership table in order to get the team(s) the submitter is in. |
| t_loop_element (as le) | le.report = r.client_id | This allows us to join the loop element table to the report (usually would be used in a join with multiple conditions to get correct data). |
| t_reports (as parent) | parent.report_id = r.parent_id | This allows us to join a child report to a parent report in order to get insights from both. |
| t_actions (as a) | a.origin = r.report_id | This allows us to match actions to the reports. |
| t_audit_events (as ae) | ae.report_id = r.report_id | This allows us to find the time at which a status of the report was changed. |
Answers Table (with answers as a)
| Table Being Joined | Join | Benefit |
| t_loop_element as (le) | le.unit_id = a.loop_path | This allows us to assign the targets set for a response to the actual response (may need multiple conditions in the actual join clause). |
| t_questions (as q) | q.element_id = a.question_id | This allows us to get information such as step and section the question is in and yes/no weight. |
| t_units (as u) | u.id = a.unit_answer | This allows us to join the unit to the unit select question they have answered. |
Actions Table (with actions as a)
| Table Being Joined | Join | Benefit |
| t_users (as u) | u.id = a.created_by | This allows us to match user information with who created the action. |
| t_reports (as r) | r.report_id = a.completed_report | This allows us to match a report to the action if the action requires them to submit one. |
| t_team (as t) | t.id = a.team | This allows us to get the information of the team which has been assigned the action. |
| t_users (as u) | u.id = a.user | This allows us to get the information of the user which has been assigned the action. |
Audit Events Table (with audit events as ae)
| Table Being Joined | Join | Benefit |
| t_users (as u) | u.id = ae.user | This allows us to get user information about who changed the report status. |
Schedules Table (with schedules as s)
| Table Being Joined | Join | Benefit |
| t_scheduled_instances (as si) | si.schedule = s.id | This allows us to match the instances generated by a schedule to the actual schedule. |
| t_units (as u) | u.id = s.subject_unit_id | This allows us to see information about a unit if the unit has been scheduled. |
| t_users (as u) | u.id = s.created_by (or s.last_modified_by) | This allows us to get information on who created or modified the schedule. |
| t_team (as t) | t.id = s.team | This allows us to get the information of the team which has been assigned the schedule. |
| t_users (as u) | u.id = s.user | This allows us to get the information of the user who has been assigned the schedule. |
| t_questions (as q) | q.workflow_id = s.workflow | This allows us to attribute questions of a workflow to the schedule. |
Users Table (with users as u)
| Table Being Joined | Join | Benefit |
| t_team_membership (as tm) | tm.member = u.id | This allows us to see what teams a user is in. |
Team Membership Table (with team membership as tm)
| Table Being Joined | Join | Benefit |
| t_teams (as t) | t.id = tm.team | This allows us to finish the above join to see what teams a user is in. |
Units Table (with units as u)
| Table Being Joined | Join | Benefit |
| t_unit_info (as ui) | ui.unit = u.id | This allows us to get unit attribute information associated with the unit. |
| t_unit_relation (as ur) | ur.child = u.id | This allows us to build the parent child relationship unit hierarchy from unit. We would then join the units table again to unit info but as ur.parent = u.id |
You could join t_unit_info to t_unit_relation in the same way but through the t_units table would be better practice.
Training Table (with training as t)
| Table Being Joined | Join | Benefit |
| t_users (as u) | u.id = t.user_id | This allows us to get the information of the user which has been assigned the training. |
