Hi,
the first thing that comes to my mind is to add a "DISTINCT" after your "SELECT".
Best regards
Andreas
Thanks, your method is useful!
But I have one more problem to solve.
When I display the results of the following SQL, I found that only a part of the members will display the results. Do you know where my code went wrong?
SELECT
cs.name,
gi.itemname,
gg.finalgrade,
CASE WHEN qz.name IS NOT NULL THEN CONCAT('http://XXX=', cm.id) ELSE NULL END AS url
FROM mdl_course_modules_completion cmc
JOIN mdl_user u ON cmc.userid = u.id
JOIN mdl_course_modules cm ON cmc.coursemoduleid = cm.id
JOIN mdl_course c ON cm.course = c.id
JOIN mdl_modules m ON cm.module = m.id
LEFT JOIN mdl_grade_items gi ON gi.courseid= c.id AND gi.itemmodule= m.name AND gi.iteminstance=cm.instance
LEFT JOIN mdl_grade_grades gg ON gg.userid=u.id AND gg.itemid=gi.id
INNER JOIN mdl_context AS ctx ON ctx.contextlevel = 70 AND ctx.instanceid = cm.id
INNER JOIN mdl_quiz AS qz ON m.name = 'quiz' AND cm.instance = qz.id
JOIN mdl_course_sections cs ON cs.id = cm.section
WHERE u.username = 943751
But I have one more problem to solve.
When I display the results of the following SQL, I found that only a part of the members will display the results. Do you know where my code went wrong?
SELECT
cs.name,
gi.itemname,
gg.finalgrade,
CASE WHEN qz.name IS NOT NULL THEN CONCAT('http://XXX=', cm.id) ELSE NULL END AS url
FROM mdl_course_modules_completion cmc
JOIN mdl_user u ON cmc.userid = u.id
JOIN mdl_course_modules cm ON cmc.coursemoduleid = cm.id
JOIN mdl_course c ON cm.course = c.id
JOIN mdl_modules m ON cm.module = m.id
LEFT JOIN mdl_grade_items gi ON gi.courseid= c.id AND gi.itemmodule= m.name AND gi.iteminstance=cm.instance
LEFT JOIN mdl_grade_grades gg ON gg.userid=u.id AND gg.itemid=gi.id
INNER JOIN mdl_context AS ctx ON ctx.contextlevel = 70 AND ctx.instanceid = cm.id
INNER JOIN mdl_quiz AS qz ON m.name = 'quiz' AND cm.instance = qz.id
JOIN mdl_course_sections cs ON cs.id = cm.section
WHERE u.username = 943751
Hi Dale
since I don't know how to open a new subject, I will ask here, since you seem to be an "expert in sql" and I know very "little" of it:
I copied a sql query which I modified for having the results between a certain date and time, but I don't seem to be able to modify the query to get the username, format the the filed time as I have it with the filed desde and what's more... what on earth is delta???? and the time ( tiempo) is minutes? seconds?. I am completely lost:
SELECT
l.id,
DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%d-%m-%y') AS Desde,
@prevtime := (SELECT MAX(timecreated) FROM prefix_logstore_standard_log
WHERE userid = %%USERID%% AND id < l.id ORDER BY id ASC LIMIT 1) AS Dtime,
IF (l.timecreated - @prevtime < 7200, @delta := @delta + (l.timecreated-@prevtime),0) AS Tiempo,
l.timecreated-@prevtime AS delta
FROM prefix_logstore_standard_log AS l,
(SELECT @delta := 0) AS s_init
# CHANGE UserID
WHERE l.userid = %%USERID%% AND l.courseid = %%COURSEID%%
AND l.timecreated > UNIX_TIMESTAMP('2020-04-01 00:00:00') # optional START DATE
AND l.timecreated <= UNIX_TIMESTAMP('2020-04-14 11:59:00') # optional END DATE
%%FILTER_STARTTIME:l.timecreated:>%% %%FILTER_ENDTIME:l.timecreated:<%%
since I don't know how to open a new subject, I will ask here, since you seem to be an "expert in sql" and I know very "little" of it:
I copied a sql query which I modified for having the results between a certain date and time, but I don't seem to be able to modify the query to get the username, format the the filed time as I have it with the filed desde and what's more... what on earth is delta???? and the time ( tiempo) is minutes? seconds?. I am completely lost:
SELECT
l.id,
DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%d-%m-%y') AS Desde,
@prevtime := (SELECT MAX(timecreated) FROM prefix_logstore_standard_log
WHERE userid = %%USERID%% AND id < l.id ORDER BY id ASC LIMIT 1) AS Dtime,
IF (l.timecreated - @prevtime < 7200, @delta := @delta + (l.timecreated-@prevtime),0) AS Tiempo,
l.timecreated-@prevtime AS delta
FROM prefix_logstore_standard_log AS l,
(SELECT @delta := 0) AS s_init
# CHANGE UserID
WHERE l.userid = %%USERID%% AND l.courseid = %%COURSEID%%
AND l.timecreated > UNIX_TIMESTAMP('2020-04-01 00:00:00') # optional START DATE
AND l.timecreated <= UNIX_TIMESTAMP('2020-04-14 11:59:00') # optional END DATE
%%FILTER_STARTTIME:l.timecreated:>%% %%FILTER_ENDTIME:l.timecreated:<%%