CODEWITHSUNDEEP
mysql
Evan Foster
Evan Foster

Reputation: 47

Get recent threads that the user did not join in to

We're trying to simply list recent threads from a table that the user in question hasn't joined in to yet.

The code below does not work (shows both new threads and threads that the user already joined in to), but shows what tables we're using:

SELECT t.*, jt.*
FROM thread t 
INNER JOIN joined_threads jt ON jt.thread_id = t.unique_id
WHERE t.owner != '$user_id'
AND jt.saved_by != '$user_id' 
GROUP BY t.unique_id

Used tables:

THREAD:
unique_id
owner
title
content
date

and

JOINED_THREADS:
saved_by
thread_id

I presume this is very easy for most of you :)

Upvotes: 1

Views: 40

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

SELECT t.*
FROM thread t
WHERE t.owner <> '$user_id'
  AND NOT EXISTS
      ( SELECT * 
        FROM joined_threads jt
        WHERE jt.thread_id = t.unique_id
          AND jt.saved_by = '$user_id' 
      )

Upvotes: 1

elevener
elevener

Reputation: 1097

If I understand what you need

SELECT t.*, jt.*
FROM thread t 
LEFT JOIN joined_threads jt ON jt.thread_id = t.unique_id AND jt.saved_by = '$user_id' 
WHERE t.owner <> '$user_id' AND jt.thread_id is NULL
GROUP BY t.unique_id

(not tested). Btw what for do you need jt.*, would be always NULL

Upvotes: 1

Related Questions