SQL:
--Find all ACTIVE staff & students
-- active staff
select sect.sectname as section, role.roledescr, pers.perslastname as lastname, pers.persfirstname as firstname,
pers.persuhuuid as uhnumber, pers.persitsusername as username, pers.persemail as email, ppos.pposmanager as supervisor
from personnel pers, persposition ppos, section sect, role
where pers.persid = ppos.persid
and pers.roleid != 9
and ppos.pposstatus = 'A'
and pers.roleid = role.roleid
and ppos.sectid = sect.sectid
UNION ALL
-- active students
select sect.sectname as section, role.roledescr, STDLINK.stdlastname as lastname, STDLINK.stdfirstname as firstname,
pers.persuhuuid as uhnumber, pers.persitsusername as username, pers.persemail as email, CONLINK.contactfirstname || ' ' || CONLINK.contactlastname as supervisor
from personnel pers, persposition ppos, section sect, role,
SECEADM.student@secedb_link STDLINK,
SECEADM.placement@secedb_link PLCLINK,
SECEADM.jobcontacts@secedb_link SUPLINK,
SECEADM.contact@secedb_link CONLINK
where pers.persid = ppos.persid
and pers.roleid = 9
and pers.persuhuuid = STDLINK.stduhuuid
and ppos.pposstatus = 'A'
and pers.roleid = role.roleid
and PLCLINK.plcStatus = 'A'
and PLCLINK.stdId = STDLINK.stdId
and PLCLINK.plcId = ppos.empId
and PLCLINK.plcjobid = SUPLINK.jobid
and SUPLINK.aprid = 2
and SUPLINK.contactid = CONLINK.contactid
and ppos.sectid = sect.sectid
order by section, roledescr, lastname, firstname;