How to make multiple encapsulated WHERE clauses
I am trying to create a chat application and I wanted to bring up the user's conversation1
and 2
.
Table:
+----+---------------------+-----------+---------+
| id | message | from_user | to_user |
+----+---------------------+-----------+---------+
| 1 | hello trick | 1 | 2 |
| 2 | hi raf i am okay | 2 | 1 |
| 3 | how is jo doing | 1 | 2 |
| 4 | Hey | 2 | 3 |
| 5 | she is doing well | 2 | 1 |
| 6 | how is kc doing | 2 | 1 |
+----+---------------------+-----------+---------+
This is my failed request:
mysql> SELECT *
-> FROM Messages
-> WHERE from_user = 1
-> AND to_user = 2
-> AND to_user = 1
-> AND from_user = 2;
Empty set (0.00 sec)
How to achieve user choice 1
and 2
. Is my project efficient for a chat application?
Expected Result:
+----+---------------------+-----------+---------+
| id | message | from_user | to_user |
+----+---------------------+-----------+---------+
| 1 | hello trick | 1 | 2 |
| 2 | hi raf i am okay | 2 | 1 |
| 3 | how is jo doing | 1 | 2 |
| 5 | she is doing well | 2 | 1 |
| 6 | how is kc doing | 2 | 1 |
+----+---------------------+-----------+---------+
ORDER BY id may be required
source to share
One row cannot have both to_user=1
, and to_user=2
, so this query will not return rows. Instead, you need to use the boolean operator or
to separate between the two "directions" of the conversation:
SELECT *
FROM messages
WHERE (from_user = 1 AND to_user = 2) OR (to_user = 1 AND from_user = 2)
source to share