0

I'm trying to check if a conversation exists between an array of user IDs and then return the conversation_id.

conversations table

+----+---------+------------+
| ID |  name   | created_at |
+----+---------+------------+
|  1 | Group 1 | datetime   |
|  2 | Group 2 | datetime   |
+----+---------+------------+

conversation_user table

+----+-----------------+---------+
| ID | conversation_id | user_id |
+----+-----------------+---------+
|  1 |               1 |       9 |
|  2 |               1 |       8 |
|  3 |               1 |       7 |
|  4 |               2 |       9 |
|  5 |               2 |       6 |
+----+-----------------+---------+

So say I have an array of user IDs [9,8,7] this would return the conversation_id of 1

And if I had an array of user IDs [8,6] this would return empty as the conversation doesn't exist.

But also if I had an array of user IDs [9,7] this would return empty because a conversation between just these two users doesn't exist either.

I've tried variances of this SQL query:

SELECT conversation_id FROM conversation_user WHERE user_id IN (9,8,7) GROUP BY conversation_id

I am struggling to get this query right. If anyone can help that would be great.

Thanks

randominstanceOfLivingThing
  • 16,873
  • 13
  • 49
  • 72
Levi Cole
  • 3,561
  • 1
  • 21
  • 36
  • @vkp Yes that question wasn't well explained so I have opened this new question to help give a better understanding of my issue and aim. Thanks. – Levi Cole Apr 27 '16 at 20:02
  • you can edit the same question with a better explanation instead of opening a new one.you already have an answer there. – Vamsi Prabhala Apr 27 '16 at 20:03

0 Answers0