Reputation: 29
I have two tables for an app with a conversation feature. It has a sessions table which holds conversation sessions and a session participants table that holds the participants for a conversation. Now, I want to get all the other participants that I have a conversation with.
`sessions` table
+----+---------+
| id | session |
+----+---------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+---------+
`session_participants` table
+----+---------+------+
| id | session | user |
+----+---------+------+
| 1 | A | 1 |
| 2 | A | 2 |
| 3 | B | 3 |
| 4 | B | 2 |
| 5 | C | 1 |
| 6 | C | 4 |
+----+---------+------+
Let say my id is 1, how can I get users with the id 2 & 4 which I have a conversation with in the sessions A & C?
Thank you for your responses.
Upvotes: 0
Views: 36
Reputation: 782785
SELECT sp2.user
FROM session_participants AS sp1
JOIN session_participants AS sp2 ON sp2.session = sp1.session AND sp2.id != sp1.id
WHERE sp1.user = 1
Upvotes: 1