Moodle SQL: Question Regarding Joining 2 Tables

Question

I am attempting to write a SQL query in Moodle that shows me when a student has enrolled as well as completed a specific course. If the Completion Date is null, then it's assumed that student hasn't completed the course. This is the kind of data I want to capture. This is what I have so far:

SELECT u.lastname, u.firstname, u.username, c.fullname, 
FROM_UNIXTIME(cx.timecompleted) as "Completion Date",Null as "Enrol_Time"

FROM prefix_course_completions as cx
JOIN prefix_course as c on cx.course = c.id
Join prefix_user as u on cx.userid = u.id

Where cx.timecompleted is not null

and c.fullname like "%refresh%"

union 

SELECT u.lastname, u.firstname, u.username, c.fullname, 
FROM_UNIXTIME(cc.timecompleted) as "Completion Date", Null as "Enrol_Time"

From prefix_local_recompletion_cc_cc as cc
JOIN prefix_course as c on cc.course = c.id
Join prefix_user as u on cc.userid = u.id

and c.fullname like "%refresh%"

Where cc.timecompleted IS NOT NULL

union 

SELECT u.lastname, u.firstname, u.username, co.fullname, Null as "Completion 
Date",

FROM_UNIXTIME(ra.timemodified,'%D %M %h:%i:%s %x') AS enrol_time FROM 
prefix_role_assignments ra, 
prefix_user u, prefix_context c, prefix_course co
WHERE ra.contextid = c.id AND c.contextlevel = 50 AND ra.userid = u.id AND 
c.instanceid = co.id AND ra.timemodified >0

and co.fullname like "%refresh%"

order by "Completion Date" DESC

The above query gives me all of the data I want (Student name, Course name, Enrollment / Completion Date). However as it stands, the Enrollment Date and Completion date are on separate rows.

Does anyone have any advice / insight on how I can get the Enrollment and Completion Dates on the same row of the table? I imagine it's some kind of JOIN query, but I am not sure.

Any help would be very much appreciated. Thanks.

I think it is better if you could provide some simplified sample data and the expected output. It is easier for us given the fact that we do not know anything about the table schemas. — Tony, Dec 18 '18 at 17:48
what you are looking for is pivot, which is not available in mysql. but there are ways of doing this. check [this link](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Derviş Kayımbaşıoğlu, Dec 18 '18 at 17:54

score 1 · Answer 1 · answered Dec 22 '18 at 15:59

Something like this will list enrolled users and their completion status.

SELECT c.id AS courseid, c.fullname AS coursename,
    u.id AS userid, u.firstname, u.lastname,
    e.enrol AS enrolmethod, ue.timecreated AS timeenrolled,
    cc.timecompleted,
CASE
    WHEN cc.status = 10 THEN 'Not yet started'
    WHEN cc.status = 25 THEN 'In progress'
    WHEN cc.status = 50 THEN 'Completed'
    WHEN cc.status = 75 THEN 'Completed via Record of Prior Learning'
    ELSE 'Unknown'
END AS completionstatus
FROM mdl_course c
JOIN mdl_enrol e ON e.courseid = c.id
JOIN mdl_user_enrolments ue ON ue.enrolid = e.id
JOIN mdl_user u ON u.id = ue.userid
JOIN mdl_course_completions cc ON cc.course = c.id AND cc.userid = u.id

Note that a user can be enrolled on a course using more than 1 enrollment method, so there might be more than one row for a user. Also a user can have a completion record but has been unenrolled from the course, so they won't be included. So maybe change the join to:

FROM mdl_course c
JOIN mdl_course_completions cc ON cc.course = c.id
JOIN mdl_user u ON u.id = cc.userid
LEFT JOIN (
    SELECT ue.userid, e.courseid, MIN(ue.timecreated) AS timecreated
    FROM mdl_enrol e
    JOIN mdl_user_enrolments ue ON ue.enrolid = e.id
    GROUP BY ue.userid, e.courseid
) ue ON ue.userid = u.id AND ue.courseid = c.id

Moodle SQL: Question Regarding Joining 2 Tables

1 Answers1