Search code examples
mysqlforum

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'
Solution

  • 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;