SQL Script to Generate a List of Sections (& Instructors) from Blackboard Learning System CE8

We need to generate a list of all courses/ sections hosted in our Learning Management System (powered by Blackboard Learning System CE8, formerly known as WebCT).

In Blackboard's terminology, all courses and sections are learning contexts. They are hierarchical and their parent-child relationships along with other information are stored in the database. So, the challenge here is to figure out how to write a recursive query.

With a little help from an msdn article: Recursive Queries Using Common Table Expressions, I figured out how to do that today.

The following SQL query will generate a list of all sections ordered by the course names, and the by section names:

with temp1 (parent_learning_context_id, learning_context_id, name, level)
as
(
select parent_learning_context_id, learning_context_id,
name, 0 as level from rpt_learning_context

where parent_learning_context_id IS NULL

union all

select LC.parent_learning_context_id,
LC.learning_context_id, LC.name, level + 1

from rpt_learning_context as LC

inner join temp1 as T

on LC.parent_learning_context_id = T.learning_context_id

)

select T.parent_learning_context_id, LC.name,
T.learning_context_id, T.name from temp1 as T

inner join learning_context as LC

on T.parent_learning_context_id = LC.id

where T.level = 4

order by LC.name, T.name

With a little twist, I can easily modify it to generate a list of all instructors teaching this Spring semester:

with temp1 (parent_learning_context_id, learning_context_id, name, level)

as

(

select parent_learning_context_id, learning_context_id,
name, 0 as level from rpt_learning_context

where parent_learning_context_id IS NULL

union all

select LC.parent_learning_context_id,
LC.learning_context_id, LC.name, level + 1
from rpt_learning_context as LC

inner join temp1 as T

on LC.parent_learning_context_id = T.learning_context_id

)

select LC.name, T.name, P.webct_id_lowercase,
P.name_n_given, P.name_n_family, P.email from temp1 as T

inner join learning_context as LC

on T.parent_learning_context_id = LC.id

inner join rpt_member as M

on M.learning_context_id = T.learning_context_id

inner join person as P

on M.person_id = p.id

where T.level = 4

and LC.name like '%2009_spring%'

and M.role='SINS'

and M.active = '1'

and M.denied_access = '0'

order by T.name

SQL queries - I'm loving it.