Custom SQL pulling multiple lines for each user

Custom SQL pulling multiple lines for each user

by Keith Murray -
Number of replies: 2

Good Morning All,

I have finally pieced a report together that is pulling my scorm completions and also listing my custom profile fields but it is also generating multiple (10 or more) lines in the resulting report for each user for the same scorm.  Any ideas on errors I'm missing is greatly appreciated!!  Thank you, 


SELECT u.lastname, u.firstname, u.email, uid.data AS comp, uid2.data AS post, uid3.data AS detachment, c.shortname, 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

JOIN prefix_user_info_data AS uid ON uid.userid = u.id

JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id

JOIN prefix_user_info_data AS uid2 ON uid2.userid = u.id

JOIN prefix_user_info_field AS uif2 ON uid2.fieldid = uif2.id

JOIN prefix_user_info_data AS uid3 ON uid3.userid = u.id

JOIN prefix_user_info_field AS uif3 ON uid3.fieldid = uif3.id

WHERE st.VALUE='completed' 

ORDER BY c.fullname, u.lastname,u.firstname, st.attempt


Average of ratings: -
In reply to Keith Murray

Re: Custom SQL pulling multiple lines for each user

by Keith Murray -

Figured this out.  See code below to help anyone else.  

Only issue is report does not show the non-attempts of the scorm which I need to pull.  Any ideas would be most appreciated smile

SELECT u.lastname, u.firstname, u.email, uid.data AS comp, uid2.data AS post, uid3.data AS detachment, c.shortname, st.attempt Attempt,st.VALUE 

STATUS,FROM_UNIXTIME(st.timemodified,"%m-%d-%Y") DATE 

FROM prefix_scorm_scoes_track AS st 

JOIN prefix_scorm AS sc ON sc.id=st.scormid

JOIN prefix_course AS c ON c.id=sc.course

JOIN prefix_user AS u ON st.userid=u.id

JOIN prefix_user_info_data AS uid ON uid.userid = u.id

JOIN prefix_user_info_field AS uif ON (uid.fieldid = uif.id AND uif.shortname = 'comp')

JOIN prefix_user_info_data AS uid2 ON uid2.userid = u.id

JOIN prefix_user_info_field AS uif2 ON (uid2.fieldid = uif2.id AND uif2.shortname = 'post')

JOIN prefix_user_info_data AS uid3 ON uid3.userid = u.id

JOIN prefix_user_info_field AS uif3 ON (uid3.fieldid = uif3.id AND uif3.shortname = 'detachment')

WHERE uif.shortname = 'comp' and uif2.shortname = 'post' and uif3.shortname = 'detachment' and c.id = 3 and st.value IN ('incomplete','completed',)

ORDER BY c.fullname, u.lastname,u.firstname, st.attempt


Average of ratings: -
In reply to Keith Murray

Re: Custom SQL pulling multiple lines for each user

by Peter Bowen -
Hi Keith,

Because you have started with the scorm_scoes_track, you will only get items which exist in there, that is, students who have attempted the SCORM.

If you start with user, then you will start with each user, then join their info data, then finally LEFT JOIN (as opposed to JOIN which means INNER JOIN) the scorm_scoes_track it will then give you people who have attempted the SCORM, as well as those who have not.

This will however pull everyone in your database, which is fine if everyone was supposed to do the SCORM, otherwise you may need to look at only those who are enrolled in the course which the SCORM lives.

Hope this helps.

Cheers
Peter
Average of ratings: -