Hello using only 1 query I need to select the count of all the messages inside conversations that user participate.
At the moment I have 2 conversations. The first conversation has a total of 8 messages and has 3 participants: 1, 2 and 3. The second conversation has only 1 message and has 2 participants: 2 and 3.
My expected result is:
- User 1, 8 messages (he is only in first conversation)
- User 2, 9 messages (he is in both conversations)
- User 3, 9 messages (he is in both conversations)
My current query is:
SELECT c.cid, cu.uid, COUNT(*) AS messages
FROM conversations c
JOIN conversations_users cu ON (cu.uid IN (1,2,3) AND cu.cid = c.cid)
LEFT JOIN conversations_messages cm ON (cm.cid = c.cid)
GROUP BY cm.cid, cu.uid
but its result is:
| cid | uid | messages |
|---|---|---|
| 3 | 1 | 8 |
| 3 | 2 | 8 |
| 3 | 3 | 8 |
| 4 | 2 | 1 |
| 4 | 3 | 1 |
