1

I have one table with this data :

enter image description here

I would like to get the thread_id shared only by the user_id 140 and 1.

So in my example the result would be 356 and 358.

With this request

select a.user_id , b.user_id, a.thread_id 
from `wp_bp_messages_recipients` as a, `wp_bp_messages_recipients` as b 
where a.thread_id = b.thread_id 
and a.user_id = 1 
and b.user_id = 140

I have 356, 358, 359, but i don't want the thread_id 359 because it is shared with the user_id 5.

Thank's a lot for your help !

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Jean
  • 48
  • 7

1 Answers1

0

You can use GROUP BY with distinct COUNT, e.g.:

SELECT thread_id
FROM table_name
WHERE thread_id NOT IN (SELECT thread_id FROM table_name WHERE user_id NOT IN (1, 140))
GROUP BY thread_id
HAVING COUNT(DISTINCT(user_id)) = 2;
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • Thanks, but i'm getting the same result with this request : http://imgur.com/a/CB9wI (testing on phpmyadmin) – Jean May 26 '17 at 22:29
  • Thanks a lot it's working :) It's the fastest request possible ? I have a huge table. – Jean May 26 '17 at 22:38
  • I have removed one condition, try now? Yes, it should be fine as long as `thread_id` column is indexed. – Darshan Mehta May 26 '17 at 22:41
  • it's still working, and a bit faster, still if you have an idea with one select i will take if, thank you for your time ! – Jean May 26 '17 at 22:45
  • Can't think of any atm, however, will update the answer if I find out anything else. – Darshan Mehta May 26 '17 at 23:05