SELECT u.id,u.picture,u.firstname,u.lastname,u.firstnamephonetic,u.lastnamephonetic,
u.middlename,u.alternatename,u.imagealt,u.email,u.username,u.city,u.country,u.lang,
u.timezone,u.maildisplay, COALESCE(ul.timeaccess, 0) AS lastaccess, ctx.id AS ctxid,
ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel,
ctx.instanceid AS ctxinstance
FROM table_user u JOIN
( SELECT DISTINCT eu1_u.id, d.data, ouil_en.hidden
FROM table_user eu1_u
JOIN table_user_enrolments eu1_ue ON eu1_ue.userid = eu1_u.id
JOIN table_enrol eu1_e ON (eu1_e.id = eu1_ue.enrolid AND eu1_e.courseid = '21541')
JOIN table_user_info_data d ON ( eu1_u.id = d.userid)
JOIN table_user_info_field f ON ( d.fieldid = f.id AND f.shortname = 'oufirstname')
JOIN table_ouil_user_enrolments ouil_en ON (ouil_en.userenrolid = eu1_ue.id)
WHERE eu1_u.deleted = 0 AND eu1_u.id <> '2' AND eu1_ue.status = 0 AND eu1_e.status = 0
AND eu1_ue.timestart < '1407765600' AND (eu1_ue.timeend = 0 OR eu1_ue.timeend > '1407765600') ) e ON e.id = u.id
LEFT JOIN table_user_lastaccess ul ON (ul.userid = u.id AND ul.courseid = '21541')
LEFT JOIN table_context ctx ON (ctx.instanceid = u.id AND ctx.contextlevel = '30')
this one is waaaay over my head.
all I need is to add the value of the field “hidden” that is in “table_ouil_user_enrolments ouil_en” to the output.
it’s already in the sub query.
can you help ?