ID | Name_ID |  Class | Teacher | Student
1       1         1       N         Y
2       2         1       N         Y
3       3         1       Y         N
4       4         2       N         Y
5       5         2       N         Y
6       5         2       Y         N
7       2         3       Y         N

ID  |  First_Name  |  Last_Name |
1      Tom            Smith
2      Mike           Collins
3      Chris          Stewart
4      Paul           McCartney
5      Ringo          Starr

ID  |  First_Name   | Last_Name |
1      First          Teacher
2      Second         Teacher
3      Becky          Camp
4      Fourth         Teacher
5      Mike           Brazil

select c.id, s.first_name + ' ' + s.last_name as 'Student Name', t.first_name + ' ' + t.last_name as 'Teacher Name'
from class c
left join students s on c.name_id = s.id and c.Student = 'Y'
left join teachers t on c.name_id = t.id and c.Teacher = 'Y'

Class | Student Name   |  Teacher Name  |
1       Tom Smith         Becky Camp
1       Mike Collins      Becky Camp
2       Paul McCartney    Mike Brazil
2       Ringo Starr       Mike Brazil
3       NULL              Second Teacher

Class | Student Name   |  Teacher Name  |
1       Tom Smith         Becky Camp
1       NULL              Becky Camp
1       Mike Collins      NULL
1       Mike Collins      Becky Camp
1       NULL              Becky Camp
1       Tom Smith         NULL
2       Paul McCartney    Mike Brazil
2       NULL              Mike Brazil
2       Paul McCartney    NULL
2       Ringo Starr       Mike Brazil
2       NULL              Mike Brazil
2       Ringo Starr       NULL