I'm sure my title is not clear enough...
Let's say I have a "chat" table, storing individual messages like this:
message_id, from_user_id, to_user_id, timestamp
For a given user_id, I want a list of "conversations" for this user. A conversation would be all messages involving the same 2 users. Kinda of like Facebook.
What would be the best way to achieve this? I was starting to write a simple query with GROUP BY but then realized the pair of user ids could be both ways - but to me it's still the same conversation.
SELECT * FROM message WHERE (from_id = 1 OR to_id = 1) GROUP BY ... ??