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 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.

Answers:

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.

Method 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/

Method 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')


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x