CODEWITHSUNDEEP
mysqlforum
Andy Bajka
Andy Bajka

Reputation: 169

Looking for query to find where two members posted in same thread

I'm looking for a MySQL query to find where two users have posted in the same thread. Here's an example where user_id 1 and user_id 31 have both posted in thread_id 10.

example

So far I have created this query but it does not retrieve any results. The result should be thread_id 10.

SELECT thread_id
FROM post
WHERE user_id='1'
AND user_id='31'

Upvotes: 0

Views: 61

Answers (3)

Ran Eldan
Ran Eldan

Reputation: 1350

Assuming 31 and 1 where just an example, and you want to find all the thread's id who post twice or more:

SELECT thread_id
FROM post  
GROUP BY thread_id
HAVING COUNT(thread_id) > 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270391

The problem with your query is that no single row can have both users.

You can move the logic to a having clause (with a group by) to do what you want:

SELECT thread_id
FROM post
group by thread_id
having max(user_id = '1') > 0 and
       max(user_id ='31') > 0;

Upvotes: 2

sashkello
sashkello

Reputation: 17871

SELECT p1.thread_id
FROM post p1 JOIN post p2 ON p1.thread_id=p2.thread_id
WHERE p1.user_id='1' AND p2.user_id='31'
GROUP BY p1.thread_id

or

SELECT p1.thread_id
FROM post p1
WHERE p1.user_id='1' 
      AND EXISTS(SELECT * FROM post p2 WHERE p2.thread_id=p1.thread_id AND p2.user_id='31')
GROUP BY p1.thread_id

Upvotes: 1

Related Questions