I've been working on a single SQL query to pull all Teachers (some courses have multiple) as well as course short name and only meta as the enrol type. I've got a working query, but it seems very inefficient to me. Would love some feedback and suggestions from a more skilled db admin. I'm sure I'm missing an obvious concept that would simplify and make this query much more efficient. Thanks in advance!
select distinct u.username, u.email, u.firstname, u.lastname, c.shortname, e.enrol
from mdl_user u
join mdl_role_assignments ra on ra.userid = u.id
join mdl_context x on x.id = ra.contextid
join mdl_course c on c.id = x.instanceid
join mdl_enrol e on e.courseid = c.id
where u.id in (
select distinct r.userid
from mdl_role_assignments r
inner join mdl_context x on r.contextid = x.id
inner join mdl_course c on x.instanceid = c.id
where x.contextlevel = 50
and c.category = 655
and r.roleid = 3
and c.id in (
select distinct e.courseid
from mdl_enrol e
left join mdl_course c on c.id = e.courseid
where e.enrol = "meta"
and c.category = 655
)
)
and c.id in (
select distinct e.courseid
from mdl_enrol e
left join mdl_course c on c.id = e.courseid
where e.enrol = "meta"
and c.category = 655
)
and e.enrol = "meta"
