SELECT
  CR.ChangeRequestID,
  CR.ChangeRequestName AS SubjectName,
  CR.ChangeRequestDescription,
  U1.FullName AS CreatedBy,
  CR.CreateDate,
  CR.LastChangeDate,
  S1.OptionDescription AS ChangeRequestCategory,
  S2.OptionDescription AS ChangeRequestStatus,
  CR.RequestedByID AS ConsultantID
FROM
  taChangeRequest AS CR
    LEFT JOIN taUsers AS U1 ON (U1.UserID = CR.CreatedByID)
    LEFT JOIN taSystemOption AS S1 ON ((S1.OptionType = 'CHANGE-REQUEST-CATEGORY') AND (S1.OptionID = CR.ChangeRequestCategoryID))
    LEFT JOIN taSystemOption AS S2 ON ((S2.OptionType = 'CHANGE-REQUEST-STATUS') AND (S2.OptionID = CR.ChangeRequestStatusID))
WHERE
  (CR.ChangeRequestID = @nChangeRequestID)
