The rpt_learning_context table includes a handy little column called path. This column contains the hierarchical path to the learning context. This is really useful if you want to find information about descendants of a particular learning context.

For example, if you want to find all section-level tracking events within the institution named "Institution Name", you could probably come up with some complicated recursive query or you could simply search for tracking events in all section learning contexts that have "Institution Name" in their paths. The following SQL query does just that.

# Select all section-level and login (institution-level)
# events for the given user ID within an institution.
SELECT trk.action_name,
trk.tool_name,
trk.page_name,
lcx.learning_context_id,
lcx.name,
trk.event_time,
prs.user_id,
lcx.path
FROM rpt_tracking trk, rpt_learning_context lcx, rpt_person prs
WHERE
prs.person_id = trk.person_id AND
lcx.learning_context_id = trk.learning_context_id AND
lcx.title = "Section" AND
INSTR(lcx.path, "Institution Name", 1, 1) > 0
ORDER BY trk.event_time;

This article originally authored by Paul Monk on the WebCT DevNet