To generate a report that shows the names and districts of teachers who have attended a certain event, the following query gives me just what I need:
SELECT p.FirstName, p.LastName, d.DistrictName
FROM Registration r
INNER JOIN (People p
LEFT OUTER JOIN Districts d
ON p.DistrictID = d.DistrictID)
ON r.ParticipantID = p.ParticipantID
WHERE r.EventID = #EventID#
However, when I want to also show the teacher's school, I run into a block. The school data is stored just as the district data is stored -- the People table contains just the ID, and names are stored in a separate table.
SELECT p.FirstName, p.LastName, d.DistrictName
FROM Registration r
INNER JOIN (People p
LEFT OUTER JOIN Districts d
ON p.DistrictID = d.DistrictID)
ON r.ParticipantID = p.ParticipantID
WHERE r.EventID = #EventID#
However, when I want to also show the teacher's school, I run into a block. The school data is stored just as the district data is stored -- the People table contains just the ID, and names are stored in a separate table.