select distinct eo1.NameOfInstitution as District,eo.NameOfInstitution as School, s.LastSurname, s.FirstName
, case when seoa.StaffUSI is not null then 'X' else ' ' end as StaffEdOrgAssn
, case when ssa.StaffUSI is not null then 'X' else ' ' end as StaffSchAssn
, case when ssec.StaffUSI is not null then 'X' else ' ' end as Sections
, case when sic.StaffUSI is not null then 'X' else ' ' end as HasPIC
, case when sic2.StaffUSI is not null then 'X' else ' ' end as HasDistrictStaffNumber
, case when sem.StaffUSI is not null then 'X' else ' ' end as HasEmail
from edfi.Staff s
left join edfi.StaffSchoolAssociation ssa on ssa.StaffUSI=s.StaffUSI
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=ssa.SchoolId
left join edfi.StaffEducationOrganizationAssignmentAssociation seoa on seoa.StaffUSI=s.StaffUSI
left join edfi.EducationOrganization eo1 on eo1.EducationOrganizationId=seoa.EducationOrganizationId
left join edfi.StaffSectionAssociation ssec on ssec.StaffUSI=s.StaffUSI
left join edfi.StaffIdentificationCode sic on sic.StaffUSI=s.StaffUSI and sic.StaffIdentificationSystemDescriptorId in (select di.DescriptorId from edfi.Descriptor di where di.Namespace like '%StaffIdentification%' and di.CodeValue='State')
left join edfi.StaffIdentificationCode sic2 on sic2.StaffUSI=s.StaffUSI and sic2.StaffIdentificationSystemDescriptorId in (select di.DescriptorId from edfi.Descriptor di where di.Namespace like '%StaffIdentification%' and di.CodeValue='District')
left join edfi.StaffElectronicMail sem on sem.StaffUSI=s.StaffUSI
where seoa.StaffUSI is null or ssa.StaffUSI is null or ssec.StaffUSI is null or sic.StaffUSI is null or sic2.StaffUSI is null or sem.StaffUSI is null
order by 1,2,s.LastSurname,s.FirstName