PHP Select Query using NOT IN statement

Question

I'm having some difficulty with a SELECT query that should be rather easy. Here's the simplified background:

I have two tables in my database. One table, named TABLE1, contains three fields: [QuestionID], [Question], [Answer]. The second table, named TABLE2, contains two fields: [UserID] and [QuestionID].

Basically, the functionality of the page is when a user answers a question correctly, the username and question ID will inserted into TABLE2. TABLE2 will be used to omit answered questions from the page so that the user will only be able to answer questions that have not been answered.

So, the SELECT query should work by 'Displaying all questions in TABLE1 that are NOT IN TABLE2'. This sounds very easy, but I can't figure it out.

Here's my queries:

 $answered = mysql_query("SELECT QuestionID FROM TABLE2 WHERE Username='TESTING'");
 while ($answered1 = mysql_fetch_array($answered)) 
 {
 $QuestionsToDisplay = mysql_query("SELECT * FROM TABLE1 WHERE 'QuestionID' NOT IN ('$answered1')");
 }
 }

I also tried:

$QuestionsToDisplay = mysql_query("SELECT * FROM TABLE1 WHERE 'QuestionID' !='$answered1'");

Both instances display one result when it should be displaying 4.

I put the first query in a 'while' statement because it returned the right data (I have two entries in TABLE2 for testing purposes, and that's what the echo statement displays).

So any idea what I'm doing wrong?

Thanks!!

Answer 1

Why not just use joins, I wonder?

    SELECT t2.QuestionID 
      FROM table2 t2 
INNER JOIN table1 t1 ON t1.QuestionID = t2.QuestionID
     WHERE t2.Username = 'TESTING'

With this query you will effectively exclude all the table2 records that don't have corresponding rows in table1 from the result rowset.

Answer 2

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

You need to learn how to use a JOIN. I'm also not sure how you're using username. This query will display all questions in TABLE1 that are not in TABLE2.

SELECT a.QuestionID
FROM TABLE1 a
LEFT JOIN TABLE2 b ON b.QuestionID = a.QuestionID
WHERE b.QuestionID IS NULL

See it in action

Answer 3

I think you should consider using a LEFT JOIN:

SELECT t1.* 
FROM TABLE1 t1
LEFT JOIN TABLE2 t2
    ON t1.QuestionID = t2.QuestionID
    AND T2.Username = 'TESTING'
WHERE t2.QuestionId is null

This will return everything in Table1 that does not have a corresponding record in Table2

Answer 4

Don't put QuestionID in quotes! Otherwise MySQL will be checking for the literal value'QuestionID' in TABLE2, not the column value.