Left hand joins?

[Siegfried Heintze]

I presently have a one-to-many relationship between teachers and students.
An SQL Select statement performs a join and displays the students assigned
to a given teacher.

Also have a zero-to-many relationship between projects and students. I
presently have a second SQL statement that displays all the students
assigned to a given project. Not all students are assigned to a project,
however, so this table is never bigger than the first.

My client now wants a single display of all the students assigned to a given
teacher (like the first display). He also wants a column that indicates if
they are assigned to a given project. What would the SQL statement look like
for this?

Thanks,
Sieg

 

[Sam D]

I'm not all that clear about your design. Am I correct to say that the
Students table contains a secondary key to both the Teachers table PK (say
TeacherID) and also to the Project table PK (say ProjectID)? If so then the
SQL would be something like....

SELECT Students.StudentName, Teachers.TeacherName, Projects.ProjectName
FROM (Students INNER JOIN Teachers ON Students.TeacherID =
Teachers.TeacherID) LEFT JOIN Projects ON Students.ProjectID =
Projects.ProjectID

HTH
Sam