Filter List B that uses Lookup Field in List A using another Column in List A

Question

List A is a list of employees with contact information and a column that indicates if the person is still active. List B is a list of projects assigend to employees using a lookup field to select the employee from List A.

If the employee is no longer active, I don't want the project showing up in the list. How can I do this?

FYI I am using SP2007 and do not have SP Designer.

Answer 1

If you want them in your view, then in your lookup column... you can get IsActive column as well on ListB... and column will then be associated with ListB as Employee:IsActive... then you can filter your view on this column!

If you are want to utilize them in your code, then you can use SPQuery.Join and ProjectedFields as shown here:
List Joins and Projections