Checking same element in two tables

Hi. here is the thing. I have two tables: threads and threads_watched.

Threads structure: id, name, time_created
Threads_watched structure: id, thread_id, time_watched

Basically what I want to do is to check if a thread from 'threads' does not exist in threads_watched table.

For example, Threads table rows:
id, name, time_created
'1', 'My 1st thread', '125788952'
'2', 'My 2nd thread', '125788966'
'3', 'My 3rd thread', '125788970'

Threads_watched rows:
id, thread_id, time_watched
'1', '1', '1257899999'
'2', '2', '1257899999'

How do I check that thread with ID3 does not exist so I can sign that forum as unread. I am making this in forum index page. Thanks.

 

I'm not a mySQL expert and someone else could probably write this better but I would think something like:

SELECT id FROM Threads WHERE id NOT IN (SELECT id FROM Threads_Watched)

 

You need a left inner join--show all results from one table no matter if there are matches in a second table. Then you count the total number of records in the second table and apply criteria to just show those ids that have 0 results in the second table.

SELECT threads.id, Count(threads_watched.thread_id) AS totalthreads
FROM threads LEFT JOIN threads_watched ON threads.id = threads_watched.thread_id
GROUP BY threads.id
HAVING (Count(threads_watched.thread_id)=0);

Code (markup):

 

Inner join would be my preferred way to deal with this. An inner join prevents an unnecessary where clause.

 

Inner joins are faster as well. IN query at times proves to be memory hogger and server killer. IN queries must be used wisely and timely.