Reputation: 23
I have two table which looks like this:
conversations
conversation_id | type | updated_at
50 | private | 2018-01-01 15:50:51
30 | group | 2019-01-01 15:50:49
100 | private | 2018-01-01 15:50:55
and
conversation_participants
user_id | conversation_id
1 | 100
3 | 50
5 | 99
6 | 50
6 | 30
3 | 30
2 | 30
How can I now select the conversation_id
depending on two user_id
and the type of the conversation?
For example, give me the conversation_id
from user 3 and 6 which is private. So i would get conversation_id
50.
My approach so far:
SELECT *
FROM conversation_participants as cp
LEFT JOIN conversations as c
ON c.conversation_id = cp.conversation_id
WHERE c.type = 'private'
But how can I fetch the conversation_id
depending on different rows?
Upvotes: 2
Views: 40
Reputation: 2341
I would suggest you start by selecting conversations based on your participants. For example:
SELECT *
FROM conversation_participants cp
WHERE cp.user_id IN (3, 6)
To also include only private conversations, you'll have to join conversation_participants
to conversations
:
SELECT *
FROM conversation_participants cp
INNER JOIN conversations c ON
c.conversation_id = cp.conversation_id
WHERE
cp.user_id IN (3, 6)
AND c.type = 'private'
Of course, this query will include a row for each user in the conversation. If you want to only have 1 row, you can group by conversation. Something like this:
SELECT
c.conversation_id,
c.updated_at,
COUNT(*) AS participant_count
FROM conversation_participants cp
INNER JOIN conversations c ON
c.conversation_id = cp.conversation_id
WHERE
cp.user_id IN (3, 6)
AND c.type = 'private'
GROUP BY
c.conversation_id,
c.updated_at
You'll need to use the GROUPING
clause, and also add a HAVING
clause, to refine your query (especially if you want to search for a conversation specifying more than 2 participants):
SELECT
c.conversation_id,
COUNT(*) AS participant_count
FROM conversations c
INNER JOIN conversation_participants cp ON
cp.conversation_id = c.conversation_id
WHERE
c.type = 'private'
AND cp.user_id IN (3, 6)
GROUP BY c.conversation_id
HAVING COUNT(*) = 2
Upvotes: 0
Reputation: 48207
First you need to know which converstion are private
SELECT conversation_id
FROM conversations c
WHERE c.type = 'private'
Then you need to see what conversation have users 3 and 6 using a SELF JOIN
SELECT conversation_id
FROM conversation_participants cp1
JOIN conversation_participants cp2
ON cp1.conversation_id = cp2.conversation_id
WHERE cp1 = 3
AND cp2 = 6
Now you filter only the private conversations
SELECT conversation_id
FROM conversation_participants cp1
JOIN conversation_participants cp2
ON cp1.conversation_id = cp2.conversation_id
WHERE conversation_id IN ( SELECT conversation_id
FROM conversations c
WHERE c.type = 'private' )
AND cp1 = 3
AND cp2 = 6
You can also use a third join
SELECT conversation_id
FROM conversation_participants cp1
JOIN conversation_participants cp2
ON cp1.conversation_id = cp2.conversation_id
JOIN conversations c
ON cp1.conversation_id = c.conversation_id
WHERE c.type = 'private'
AND cp1 = 3
AND cp2 = 6
Upvotes: 1