Q:

Tricky MySQL query involving NOT IN

I have two tables that I am joining together in order to get results.
One table contains a list of assessments with a unique ID (assessments), whilst the other table (staff_to_assessments) contains a list of assessments that are assigned to staff members. An example record for this table would contain the Staff member unique ID, and the assignment unique ID. This shows that a user has been linked to an assignment.  
I have written a function which only grabs assessments from the assessment table where they have not been assigned to a staff member in staff_to_assessment table. This is to populate a drop-down box on the front end where these remaining assessments can be assigned to a staff member if required, therefore I don;t want to show any that are already assigned.
MySQL query is as follows:
    SELECT * FROM assessments a 
    LEFT JOIN staff_to_assessment s2a ON a.assessment_id = s2a.assessment_id                            
    WHERE a.assessment_id NOT IN
         (SELECT assessment_id FROM staff_to_assessment WHERE staff_id = '" . (int)$options['staffId'] . "')";

This doesn't seem to generate the response I need. Can anybody see where I have gone wrong?
Thanks!!

A:

I think your query is working too hard.  You just need to find rows where there is no match to the left outer join:
SELECT a.*
FROM assessments a LEFT JOIN
     staff_to_assessment s2a
     ON a.assessment_id = s2a.assessment_id                            
WHERE s2a.assessment_id is null;

You don't need to use both a left join and a not in clause.

