SELECT where an id is not in another table
===

Basically, I have a table structure where a student can take a test.

Structure is like this:

http://www.dropmocks.com/mBj9YX

    SELECT s.name AS studentName, 
    t.name AS testName,
    MAX(sc.percentile) 
    AS percentile,
    	(CASE WHEN  MAX(sc.percentile) > 70 THEN 'passed' 
    	 WHEN MAX(sc.percentile) <70 THEN 'fail'
    	 WHEN  MAX(sc.percentile) IS_MEMBER NULL THEN 'not taken' END) AS status
    FROM Score sc

the part that does not do want I want is here:

    WHEN  MAX(sc.percentile) IS_MEMBER NULL THEN 'not taken' END

If a student does NOT take a test, the test ID will not be in the score table. I can't figure out a query that would add 'not taken' to the status column if they have not taken a test. Help? The first two work, but if there is a test ID in the test table and a student has not taken that test, then the test ID for that student will not show up in the score table. I am trying to find a way to detect that.