-- Find all Boards and the schools they Govern
-- Include the status of the school

SELECT sch.orgname AS 'SchoolName', 
	bot.orgName AS 'BoardName', 
	sch.[OrgEventStatusCode] AS Status
FROM organisation AS sch,
	orgGroup AS og ,
	organisation AS bot
WHERE bot.BusinessEntityID = og.OrgBusinessEntityID	-- get the org that is
the OWNER
AND og.MemberBusinessEntityID = sch.BusinessEntityID	-- get the org that
is the MEMBER
AND og.OrgGroupEndDate is NULL		-- NOT an ended group membership
AND og.orgRoleGroupCode = 'GOVERN'	-- Group for school boards
AND og.OrgRoleCode = 'SCHBRD'		-- Role for school board members
ORDER BY BoardName