Currently excludes dorm rooms
select substr(spriden_id, -7) id, spriden_last_name LastName, spriden_first_name FirstName, goremal_email_address Email, null Dorm, null Room, nvl2(st.gorirol_role, 1, 0) ActiveStudent, nvl2(em.gorirol_role, 1, 0) ActiveEmployee, nvl2(nd.gorirol_role, 1, 0) ActiveAffiliate from spriden left outer join gorirol st on st.gorirol_pidm = spriden_pidm and st.gorirol_role = 'STUDENT' left outer join gorirol em on em.gorirol_pidm = spriden_pidm and em.gorirol_role = 'EMPLOYEE' left outer join gorirol nd on nd.gorirol_pidm = spriden_pidm and nd.gorirol_role in ('NON_DREW_FULL', 'NON_DREW_LIMITED') left outer join goremal on goremal_pidm = spriden_pidm and goremal_emal_code = 'DREW' and goremal_status_ind = 'A' where spriden_change_ind is null and spriden_pidm in (select gorirol_pidm from gorirol where gorirol_role in ('STUDENT', 'EMPLOYEE', 'NON_DREW_FULL', 'NON_DREW_LIMITED')) union all select substr(spriden_id, -7) || 'S' id, spriden_last_name LastName, spriden_first_name FirstName, goremal_email_address Email, null Dorm, null Room, nvl2(st.gorirol_role, 1, 0) ActiveStudent, nvl2(em.gorirol_role, 1, 0) ActiveEmployee, nvl2(nd.gorirol_role, 1, 0) ActiveAffiliate from spriden left outer join gorirol st on st.gorirol_pidm = spriden_pidm and st.gorirol_role = 'STUDENT' left outer join gorirol em on em.gorirol_pidm = spriden_pidm and em.gorirol_role = 'EMPLOYEE' left outer join gorirol nd on nd.gorirol_pidm = spriden_pidm and nd.gorirol_role in ('NON_DREW_FULL', 'NON_DREW_LIMITED') left outer join goremal on goremal_pidm = spriden_pidm and goremal_emal_code = 'DREW' and goremal_status_ind = 'A' where spriden_change_ind is null and spriden_pidm in (select gorirol_pidm from gorirol where gorirol_role = 'STUDENT') and spriden_pidm in (select gorirol_pidm from gorirol where gorirol_role = 'EMPLOYEE');