select eo.NameOfInstitution,c.CohortIdentifier,c.CohortDescription,ct.ShortDescription as Type, cst.ShortDescription as Scope
,st.LastSurname as TeacherLast, st.FirstName as TeacherFirst, stca.StudentRecordAccess, s.LastSurname as StudentLast, s.FirstName as StudentFirst
from edfi.Cohort c
left join edfi.StudentCohortAssociation sca on sca.CohortIdentifier=c.CohortIdentifier and sca.EducationOrganizationId=c.EducationOrganizationId
left join edfi.StaffCohortAssociation stca on stca.CohortIdentifier=c.CohortIdentifier and stca.EducationOrganizationId=c.EducationOrganizationId
left join edfi.EducationOrganization eo on eo.EducationOrganizationId=c.EducationOrganizationId
left join edfi.CohortType ct on ct.CohortTypeId=c.CohortTypeId
left join edfi.CohortScopeType cst on cst.CohortScopeTypeId=c.CohortScopeTypeId
left join edfi.Student s on s.StudentUSI=sca.StudentUSI
left join edfi.Staff st on st.StaffUSI=stca.StaffUSI
order by eo.NameOfInstitution, st.LastSurname, st.FirstName, s.LastSurname, s.FirstName