Buenos dias Andy, lo he resuelto con el siguiente código:
SELECT
CONCAT(u.firstname ,' ',u.lastname) AS 'Desde Nombre',
CONCAT(u2.firstname ,' ',u2.lastname) AS 'Para Nombre',
DATE_FORMAT(FROM_UNIXTIME(me.timecreated), '%Y-%m-%d %H:%i') AS 'Fecha',
me.subject AS 'Asunto',
me.smallmessage AS 'Mensaje'
FROM prefix_message me
JOIN prefix_role_assignments AS ra ON ra.userid = me.useridfrom AND ra.roleid IN (3,4,5)
JOIN prefix_role_assignments AS ra2 ON ra2.userid = me.useridto AND ra2.roleid IN (3,4,5)
JOIN prefix_context AS ctx ON ra.contextid = ctx.id AND ra2.contextid = ctx.id
JOIN prefix_course AS c ON c.id = ctx.instanceid
JOIN prefix_user u ON u.id = me.useridfrom
JOIN prefix_user u2 ON u2.id = me.useridto
WHERE c.id = %%COURSEID%% AND
((ra.roleid = 3 AND ra2.roleid = 5) OR (ra.roleid = 5 AND ra2.roleid = 3))
UNION ALL
SELECT
CONCAT(ur.firstname ,' ',ur.lastname) AS 'Desde Nombre',
CONCAT(u2r.firstname ,' ',u2r.lastname) AS 'Para Nombre',
DATE_FORMAT(FROM_UNIXTIME(mer.timecreated), '%Y-%m-%d %H:%i') AS 'Fecha',
mer.subject AS 'Asunto',
mer.smallmessage AS 'Mensaje'
FROM prefix_message_read mer
JOIN prefix_role_assignments AS rar ON rar.userid = mer.useridfrom AND rar.roleid IN (3,4,5)
JOIN prefix_role_assignments AS ra2r ON ra2r.userid = mer.useridto AND ra2r.roleid IN (3,4,5)
JOIN prefix_context AS ctxr ON rar.contextid = ctxr.id AND ra2r.contextid = ctxr.id
JOIN prefix_course AS cr ON cr.id = ctxr.instanceid
JOIN prefix_user ur ON ur.id = mer.useridfrom
JOIN prefix_user u2r ON u2r.id = mer.useridto
WHERE cr.id = %%COURSEID%% AND
((rar.roleid = 3 AND ra2r.roleid = 5) OR (rar.roleid = 5 AND ra2r.roleid = 3))
con las segunda parte lo que consigo es que se vean los mensajes pendientes de leer, ya que con la primera, solo aparecian los leidos y puede que así no salgan todos al inspector que quiera cotejar esto.
Un saludo