Combine these 2 queries into 1. They work individually

101 views Asked by At

I am not a sql expert and I cannot seem to get this to work. This is for a moodle database query.

cmi.core.total_time & cmi.core.lesson_status are both under the element column in the prefix_scorm_scoes_track table. I only need entries where cmi.core.lesson_status value (next column) = completed, but I also need the value of cmi.core.total_time.

SELECT u.firstname FIRST,u.lastname LAST,c.fullname Course, st.attempt Attempt,st.value STATUS,FROM_UNIXTIME(st.timemodified,"%m-%d-%Y") DATE 
FROM prefix_scorm_scoes_track AS st 
JOIN prefix_user AS u ON st.userid=u.id
JOIN prefix_scorm AS sc ON sc.id=st.scormid
JOIN prefix_course AS c ON c.id=sc.course
WHERE st.value='completed' 
ORDER BY c.fullname, u.lastname,u.firstname, st.attempt




SELECT u.firstname FIRST,u.lastname LAST,ui.data Role,c.fullname Course, st.attempt Attempt,st.value "Completion Time",FROM_UNIXTIME(st.timemodified,"%m/%d/%y %h:%i") DATE
FROM prefix_scorm_scoes_track AS st 
JOIN prefix_user AS u ON st.userid=u.id
JOIN prefix_user_info_data AS ui ON ui.userid=u.id AND fieldid = 1
JOIN prefix_scorm AS sc ON sc.id=st.scormid
JOIN prefix_course AS c ON c.id=sc.course
WHERE st.element='cmi.core.total_time' 
ORDER BY c.fullname, u.lastname,u.firstname, st.attempt

1

There are 1 answers

2
Qazi Danish On
SELECT u.firstname FIRST,u.lastname LAST,ui.data Role,c.fullname Course, st.attempt Attempt,st.value "Completion Time",FROM_UNIXTIME(st.timemodified,"%m/%d/%y %h:%i") DATE
FROM prefix_scorm_scoes_track AS st 
JOIN prefix_user AS u ON st.userid=u.id
JOIN prefix_user_info_data AS ui ON ui.userid=u.id AND fieldid = 1
JOIN prefix_scorm AS sc ON sc.id=st.scormid
JOIN prefix_course AS c ON c.id=sc.course
WHERE st.element='cmi.core.total_time'  or st.value='completed'
ORDER BY c.fullname, u.lastname,u.firstname, st.attempt