MySQL查询一些不在另一个表中的字段

I have two tables, members and studentexam. I want to fetch the members who are not registered for an exam. It means they dont have a row in studentexa where examID is a specific value. I tried this:

$result=mysql_query("SELECT * FROM members                                      
    WHERE accessLevel='student' AND IDNo NOT IN ("SELECT studentID,examID FROM studentexam WHERE examID = $examID")
    ORDER BY family ASC");

I have two problems. First, there is a syntax error when use a sql statement into another because of ". I tried ' as well but got error again. the other, I guess if in the inner statement have just studentID then got the right result. But in that way I cant use the where clause.

Any help would be appreciated.

#1 You don't need the "" around the sub query.

#2 You don't need to SELECT a column to use it in the WHERE clause.

$result=mysql_query("SELECT * FROM members                                      
    WHERE accessLevel='student' AND IDNo NOT IN (SELECT studentID FROM studentexam WHERE examID = $examID)
    ORDER BY family ASC");

You pretty much answered your question yourself :p

In sub query you dont need to put "" just sub query direct

  $result=mysql_query("SELECT * FROM members                            
WHERE IDNo NOT IN (SELECT studentID FROM studentexam WHERE examID = $examID) ORDER BY family ASC");

And that's all you want and you don't need to write accesslevel = 'Student' as i hope that each member would have his unique ID. Writing accesslevel = 'Student' will just increase load on MySQL.

An alternative to the NOT IN in this case could be a LEFT JOIN:

SELECT * FROM members m
LEFT JOIN studentexam se ON m.idno = se.studentId
WHERE m.accessLevel = 'student' AND se.studentId IS NULL AND examId = $examID
ORDER BY m.family