SELECT chdt.changeid "Change ID",
chdt.title "Title",
orgaaa.first_name "Change Requester",
ownaaa.first_name "Change Owner",
cmDef.first_name "Change Manager",
stageDef.displayname "Stage",
statusDef.statusdisplayname "Status",
implementer.NAME "Change Implementer",
line.NAME "Change line manager",
rev.NAME "Change Reviewer",
app.NAME "Change Approver" FROM changedetails chdt
LEFT JOIN sduser orgsd
ON chdt.initiatorid = orgsd.userid
LEFT JOIN aaauser orgaaa
ON orgsd.userid = orgaaa.user_id
LEFT JOIN sduser ownsd
ON chdt.technicianid = ownsd.userid
LEFT JOIN aaauser ownaaa
ON ownsd.userid = ownaaa.user_id
LEFT JOIN change_stagedefinition stageDef
ON chdt.wfstageid = stageDef.wfstageid
LEFT JOIN change_statusdefinition statusDef
ON chdt.wfstatusid = statusDef.wfstatusid
LEFT JOIN aaauser cmDef
ON chdt.changemanagerid = cmDef.user_id
LEFT JOIN (SELECT role.changeid,
Max(us.first_name) AS NAME
FROM changeroleusermapping ROLE
LEFT JOIN aaauser us
ON ROLE.userid = us.user_id
WHERE ROLE.roleid = 7
GROUP BY ROLE.changeid)implementer
ON chdt.changeid = implementer.changeid
LEFT JOIN (SELECT role.changeid,
Max(us.first_name) AS NAME
FROM changeroleusermapping ROLE
LEFT JOIN aaauser us
ON ROLE.userid = us.user_id
WHERE ROLE.roleid = 6
GROUP BY ROLE.changeid)line
ON chdt.changeid = line.changeid
LEFT JOIN (SELECT role.changeid,
Max(us.first_name) AS NAME
FROM changeroleusermapping ROLE
LEFT JOIN aaauser us
ON ROLE.userid = us.user_id
WHERE ROLE.roleid = 8
GROUP BY ROLE.changeid)rev
ON chdt.changeid = rev.changeid
LEFT JOIN (SELECT role.changeid,
Max(us.first_name) AS NAME
FROM changeroleusermapping ROLE
LEFT JOIN aaauser us
ON ROLE.userid = us.user_id
WHERE ROLE.roleid = 5
GROUP BY ROLE.changeid)app
ON chdt.changeid = app.changeid