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

Question

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.

Answer 1

#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