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: