Select * from table1 that does not exist in table2 with conditional
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开发者_Go百科 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.
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/
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')
精彩评论