select s.LastSurname,s.FirstName,rt.Description as Relation,p.LastSurname as ParentLast, p.FirstName as ParentFirst
,(select pem.ElectronicMailAddress from edfi.ParentElectronicMail pem left join edfi.Descriptor emt on emt.DescriptorId=pem.ElectronicMailTypeDescriptorId
where pem.ParentUSI=spa.ParentUSI and emt.CodeValue='Home/Personal') as ParentHomeEmail
,(select pem.ElectronicMailAddress from edfi.ParentElectronicMail pem left join edfi.Descriptor emt on emt.DescriptorId=pem.ElectronicMailTypeDescriptorId
where pem.ParentUSI=spa.ParentUSI and emt.CodeValue='Organization') as ParentOrganizationEmail
,(select pem.ElectronicMailAddress from edfi.ParentElectronicMail pem left join edfi.Descriptor emt on emt.DescriptorId=pem.ElectronicMailTypeDescriptorId
where pem.ParentUSI=spa.ParentUSI and emt.CodeValue='Work') as ParentWorkEmail
,(select pem.ElectronicMailAddress from edfi.ParentElectronicMail pem left join edfi.Descriptor emt on emt.DescriptorId=pem.ElectronicMailTypeDescriptorId
where pem.ParentUSI=spa.ParentUSI and emt.CodeValue='Other') as ParentOtherEmail
,(select sem.ElectronicMailAddress from edfi.StudentEducationOrganizationAssociationElectronicMail sem left join edfi.Descriptor emt on emt.DescriptorId=sem.ElectronicMailTypeDescriptorId
where sem.StudentUSI=spa.StudentUSI and emt.CodeValue='Home/Personal') as StudentHomeEmail
,(select sem.ElectronicMailAddress from edfi.StudentEducationOrganizationAssociationElectronicMail sem left join edfi.Descriptor emt on emt.DescriptorId=sem.ElectronicMailTypeDescriptorId
where sem.StudentUSI=spa.StudentUSI and emt.CodeValue='Organization') as StudentUSIOrganizationEmail
,(select sem.ElectronicMailAddress from edfi.StudentEducationOrganizationAssociationElectronicMail sem left join edfi.Descriptor emt on emt.DescriptorId=sem.ElectronicMailTypeDescriptorId
where sem.StudentUSI=spa.StudentUSI and emt.CodeValue='Work') as StudentUSIWorkEmail
,(select sem.ElectronicMailAddress from edfi.StudentEducationOrganizationAssociationElectronicMail sem left join edfi.Descriptor emt on emt.DescriptorId=sem.ElectronicMailTypeDescriptorId
where sem.StudentUSI=spa.StudentUSI and emt.CodeValue='Other') as StudentUSIOtherEmail
from edfi.Student s
left join edfi.StudentParentAssociation spa on spa.StudentUSI=s.StudentUSI
left join edfi.Parent p on p.ParentUSI=spa.ParentUSI
left join edfi.Descriptor rt on rt.DescriptorId=spa.RelationDescriptorId
order by s.LastSurname,s.FirstName