select
ir.resultnum,
ir.inspformnum,
ir.revision,
ir.status as inspection_status,
ir.createdate as inspection_createdate,
ir.createdby as inspection_createdby,
p_ir_createdby.displayname as inspection_createdby_displayname,
ir.asset,
a.description as asset_description,
ir.location,
l.description as location_description,
ir.referenceobject,
ir.referenceobjectid,
ir.siteid,
iq.inspquestionnum,
iq.description as question_description,
iq.sequence as question_sequence,
ifld.inspfieldnum,
ifld.description as field_description,
ifld.fieldtype,
ifld.sequence as field_sequence,
ifr.inspfieldresultid,
ifr.inspfieldresultnum,
ifr.txtresponse,
ifr.numresponse,
ifr.enteredby,
p_ifr_enteredby.displayname as enteredby_displayname,
ifr.entereddate,
dl.doclinksid,
dl.ownertable,
dl.ownerid,
dl.document as doclinks_document,
dl.doctype as doclinks_doctype,
dl.createdate as doclink_createdate,
dl.changeby as doclink_changeby,
p_dl_changeby.displayname as doclink_changeby_displayname,
di.docinfoid,
di.document as docinfo_document,
di.description as doc_description,
di.doctype as docinfo_doctype,
di.urltype,
di.urlname,
di.contentuid,
di.createdate as docinfo_createdate,
di.createby as docinfo_createby,
p_di_createby.displayname as docinfo_createby_displayname,
di.changeby as docinfo_changeby,
p_di_changeby.displayname as docinfo_changeby_displayname
from inspectionresult ir
join inspfieldresult ifr
on ifr.resultnum = ir.resultnum
and ifr.inspformnum = ir.inspformnum
and ifr.revision = ir.revision
and ifr.siteid = ir.siteid
left join inspquestion iq
on iq.inspquestionnum = ifr.inspquestionnum
and iq.inspformnum = ifr.inspformnum
and iq.revision = ifr.revision
and coalesce(iq.siteid, ifr.siteid) = ifr.siteid
left join inspfield ifld
on ifld.inspfieldnum = ifr.inspfieldnum
and ifld.inspquestionnum = ifr.inspquestionnum
and ifld.inspformnum = ifr.inspformnum
and ifld.revision = ifr.revision
and coalesce(ifld.siteid, ifr.siteid) = ifr.siteid
join doclinks dl
on dl.ownertable = 'INSPFIELDRESULT'
and dl.ownerid = ifr.inspfieldresultid
join docinfo di
on di.docinfoid = dl.docinfoid
left join person p_ir_createdby
on p_ir_createdby.personid = ir.createdby
left join person p_ifr_enteredby
on p_ifr_enteredby.personid = ifr.enteredby
left join person p_dl_changeby
on p_dl_changeby.personid = dl.changeby
left join person p_di_createby
on p_di_createby.personid = di.createby
left join person p_di_changeby
on p_di_changeby.personid = di.changeby
left join asset a
on a.assetnum = ir.asset
and a.siteid = ir.siteid
left join locations l
on l.location = ir.location
and l.siteid = ir.siteid
where ir.siteid = 'SRQSITE'
and ir.inspformnum = '1014'
and di.doctype = 'Images'
order by
ir.resultnum,
iq.sequence,
ifld.sequence,
dl.createdate desc;