mHybk9c7

1SET @courseid = 10189
2CREATE TEMPORARY TABLE 
3IF NOT EXISTS STUDENTGRAB1 AS (
4
5
6SELECT
7u.id as userid,
8CONCAT(u.firstname, ' ', u.lastname)AS STUDENTS, 
9            u.username,
10			u.idnumber,
11            cour.fullname,
12		    cour.id AS courseid,
13            groupname
14
15            
16from mdl_user as u
17INNER JOIN mdl_user_enrolments ue ON ue.userid = u.id
18INNER JOIN mdl_enrol e ON e.id = ue.enrolid
19INNER JOIN mdl_role_assignments as ra ON ra.userid = u.id
20INNER JOIN mdl_role as r ON ra.roleid = r.id
21INNER JOIN mdl_context as con ON ra.contextid = con.id
22INNER JOIN mdl_course as cour ON cour.id = con.instanceid AND con.contextlevel = 50
23Left Outer Join 
24(select gr.name as groupname,gm.userid as gmuserid,cour.fullname as coursefullname 
25from  mdl_groups as gr
26Inner join mdl_course as cour on  gr.courseid = cour.id 
27Inner Join mdl_groups_members as gm on gm.groupid = gr.id
28Inner Join mdl_user as u on u.id = gm.userid) as groups on u.id = gmuserid and cour.fullname = coursefullname 
29and u.suspended = 0 and u.deleted = 0 and r.shortname = 'student'
30and c.id = @courseid --course
31 and r.id = 5 --role id);
32 
33 CREATE INDEX sg_userid ON STUDENTGRAB(userid);
34 CREATE INDEX sg_courseid ON STUDENTGRAB(courseid);