Putting more conditions in the join

For a basic introduction to joins, click here 

I have the following table, called tblSchool:

If the names seem strange, it is because my test data was generated automatically.

A value of -1 in the bActive column, indicates that the school is open and accepting new enrolments. A value of 0 indicates that the school has been closed.

I also have the following table of students:

Jane is enrolled in a school which has been closed (perhaps it burnt down after she was enrolled).

Simon has not been enrolled in a school at all.

The following query will return a list of all students and the schools at which they are enrolled:

SELECT tblStudent.*, tblSchool.sSchool
FROM tblStudent LEFT JOIN tblSchool
ON tblStudent.lSchoolID=tblSchool.lSchoolID

Since kweevonuh girls high school has been closed, I don't want it to appear on this list.

I could set lSchoolID to NULL on Jane's record, but I don't want to throw away the knowledge that she WAS enrolled at kweevonuh.

That information could be important, because I may later want to run an UPDATE query, to move all the students who WERE enrolled at kweevonuh, to another school. Or it might be possible that the school will be reopened.

One way of getting what I want, would be to use a CASE expression in the SELECT list:

SELECT tblStudent.*,
CASE WHEN tblSchool.bActive=-1
    THEN tblSchool.sSchool
    ELSE NULL END
FROM tblStudent LEFT JOIN tblSchool
    ON tblStudent.lSchoolID=tblSchool.lSchoolID

This second query will return the following records:

Today's more obscure tip, is that it is possible to get exactly the same result by adding an extra condition to the join expression:

SELECT tblStudent.*, tblSchool.sSchool
FROM tblStudent LEFT JOIN tblSchool
ON tblStudent.lSchoolID=tblSchool.lSchoolID
AND tblSchool.bActive=-1
ORDER BY tblStudent.lStudentID

This query works much like an ordinary LEFT JOIN, except that records from tblSchool are only joined if bActive=-1.

Adelle.