Tweeted twitter.com/#!/StackDBAs/status/452129735318204416
2 edited tags
| link
1
source | link

Join table on some conditions

I've got a table called activities. And a table messages. Activity has many messages, and one message has one activity.

This are the message columns :

id
sender_id
receiver_id
body
activity_id

I'm looking to get all the activities who have at least one message from both directions of message.

Here is what I mean, message id 1 :

id|sender_id|receiver_id|body   |activity_id
1 |20       |44         |hi     | 55 

So for activity 55 there is only one directional messaging, if you take activity messages below :

id|sender_id|receiver_id|body     |activity_id
1 |20       |44         |hi       | 55 
3 |44       |20         |hi there | 55
4 |44       |20         |who're u | 55

So above messages for activity 55 are "multi directional", meaning there are messages from both sides. In this case there are more than one message from 44 to 22 but thats ok, as long as there is at least one message from 44 to 22 and at least one message from 22 to 44 I want to get that activity_id.

I'd like to get all the activity_ids which have at least one or more message exchange from both sides.

I was thinking to do it something like this :

select distinct(activity_id) from messages
where sender_id in (
    select receiver_id from activities
    inner join messages on messages.activity_id = activities.id
    union
    select sender_id from activities
    inner join messages on messages.activity_id = activities.id
) or receiver_id in (
    select receiver_id from activities
    inner join messages on messages.activity_id = activities.id
    union
    select sender_id from activities
    inner join messages on messages.activity_id = activities.id
)

It's not really working at the moment, can someone point me to the right direction?