Select * from table1 that does not exist in table2 with conditional

Question

I have 2 tables. One is a table with things that can be learned. There is a JID that desribes each kind of row, and is unique to each row. The second table is a log of things that have been learned (the JID) and also the userid for the person that learned it. I am currently using this to select all of the data for the JID, but only the ones the user has learned based on userid.

SELECT * 
FROM tablelist1
LEFT JOIN tablelog2 ON (tablelist1.JID = tablelog2.JID) 
                       AND tablelog2.UID = 'php var'
WHERE tablelog2.JID IS NOT NULL

I now need to select the rows of things to learn, but only the things the userid has NOT already learned. I am obviously very new to this, bear with me. :) I tried using IS NULL, but while it seems it works, it gives duplicate JID's one being NULL, one being correct.

Answer 1

Using LEFT JOIN/IS NULL:

   SELECT t.*
     FROM TABLE_LIST t
LEFT JOIN TABLE_LOG tl ON tl.jid = t.jid
    WHERE tl.jid IS NULL

Using NOT IN:

SELECT t.*
  FROM TABLE_LIST t
 WHERE t.jid NOT IN (SELECT tl.jid
                       FROM TABLE_LOG tl
                   GROUP BY tl.jid)

Using NOT EXISTS:

SELECT t.*
  FROM TABLE_LIST t
 WHERE NOT EXISTS(SELECT NULL
                    FROM TABLE_LOG tl
                   WHERE tl.jid = t.jid)

FYI
LEFT JOIN/IS NULL and NOT IN are equivalent in MySQL - they will perform the same, while NOT EXISTS is slower/less efficient. For more details: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

Answer 2

First off, you should be using an INNER JOIN on your existing query:

SELECT * FROM tablelist1
    INNER JOIN tablelog2 ON (tablelist1.JID = tablelog2.JID) 
    WHERE tablelog2.UID = 'php var'

The way you're doing it you're getting all the rows from tablelist1, then going to extra trouble to exclude the ones that don't have a match in tablelog2. The INNER JOIN will do that for you, and more efficiently.

Secondly, to find for user "X" all the learnable-things that the user hasn't learned, do:

SELECT * FROM tablelist1 
    WHERE NOT EXISTS (SELECT JID FROM tablelog2 WHERE UID = 'X')