Hello Friends
I have Moodle 3.4 with MariaDB, actually i´m trying to develop a report that have custom field user and feedback response, my query is this:
SELECT
Distinct
mdl_user.id AS Userid,
CASE WHEN mdl_user_info_data.fieldid=14 THEN DATA END AS 'Level'
mdl_feedback.course AS id_course,
mdl_feedback_item.id AS Id_item,
mdl_feedback_value.value AS Response
FROM mdl_feedback_value
INNER JOIN mdl_feedback_completed ON mdl_feedback_completed.id = mdl_feedback_value.completed
INNER JOIN mdl_user ON mdl_feedback_completed.userid = mdl_user.id
INNER JOIN mdl_feedback ON mdl_feedback_completed.feedback = mdl_feedback.id
JOIN mdl_course AS c ON c.id=mdl_feedback.course
JOIN mdl_course_categories AS cc ON cc.id=c.category
JOIN mdl_user_info_data ON mdl_user_info_data.userid=mdl_user.id
INNER JOIN mdl_feedback_item ON mdl_feedback_value.item = mdl_feedback_item.id
WHERE c.idnumber IN (72007205)
AND mdl_feedback.name LIKE '%Encuesta de Cierre%'
And the result is
The first result for Userid in level is null but the second is level with value 7.
In the third element is the same result, the level is null, but in the fourt element the level is 7.
I tried to add Distinct in Case sentence but when i add Distinct i have the next error.
If i add Group by mdl_user.id i have another error.
Somebody can helpe to fix my query please?
Thanks.