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.
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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)
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')