how can i find unique conversation from table in sql

user_14517269 0
Viewed 378

I Have this table:

[Messages table]

1

I need to find the number of uniqe conversation - conversation is define as senderid sent msg to reciverid, and reciverid has replied (no matter how many times or the thread length, it will be count as 1 conversation). so if senderid = 1, reeiver id =2 and in the next row senderid = 2 and reciever id =1 this is one conversation (till the end of time) Im really stock and not sure how to proceed.

Thanks!

mysql sql
2 Answers

You can use the functions LEAST() and GREATEST() to create unique combinations of the 2 ids and aggregate:

SELECT COUNT(DISTINCT LEAST(m1.senderid, m1.receiverid), GREATEST(m1.senderid, m1.receiverid)) counter
FROM Messages m1
WHERE EXISTS (SELECT 1 FROM Messages m2 WHERE (m2.receiverid, m2.senderid) = (m1.senderid, m1.receiverid))

See the demo.
Results (for your sample data):

counter
2
user_104988280

Here's one option using a self join with least and greatest to get your desired results:

select count(*)
from (
    select distinct least(m1.senderid, m2.senderid), greatest(m1.receiverid, m2.receiverid)
    from messages m1
        join messages m2 on m1.senderid = m2.receiverid and m1.receiverid = m2.senderid
) t
user_10736310
Related