How do I select just mutual rows in SQL?

I have a table with a source_id and destination_id and a message and I want to group messages together. There can only be one message between a given source_id and destination_id, but I only want rows that have a mutual response for a given ID (say id 1). In the following examples, I want rows #1, #2, #4 and #5 because there is a mutual response between id 1 and 2 and between id 1 and id 4 (id 1 sent a message to id 2 and id 2 sent a message to id 1, similarly, id 1 sent a message to id 4 and id 4 sent a message to id 1). I don’t want id 3 because it has no mutual response.

How do I select this in SQL? (I’m using PostgreSQL btw)

Example:
table messages
#  source_id destination_id message
1  1          2             hello
2  2          1             hi
3  1          3             bye
4  1          4             thanks
5  4          1             okay
6  3          5             blablabla
7  5          3             hooray

Preferably, I want my select to return these 4 rows:

1  1          2             hello
2  2          1             hi
3  1          4             thanks
4  4          1             okay

Thanks in advance 🙂

Answer

You seem to be describing exists:

select m.*
from messages m
where exists (select 1
              from messages m2
              where m2.source_id = m.destination_id and
                    m2.source_id = m.destination_id
             );

In your example, this would also return rows 6 and 7, because those seem to follow the rule you specified.

If you want 1 to be one of the ids, then include a filter for that:

select m.*
from messages m
where 1 in (m.source_id, m.destination_id) and
      exists (select 1
              from messages m2
              where m2.source_id = m.destination_id and
                    m2.source_id = m.destination_id
             );