MySQL：AND运算符不起作用

Question

I have chat database contains from three table (users,message,message_recipient) and their relations here : Database Design the content of table is :

tabel users:

us_id username password 1 user1 pass1 2 user2 pass2 3 user3 pass3

table mesasage

id  msg_body   create_date  creator_id
1   "Hi user2" ---------      1
2   "Hi user3  ---------      1

table message_recipient

id  repient_id  message_id is_read
1   2           1          0
2   3           2          0

my problem is when select the messages that user1 send to user2 the AND operator not working or there is bug with AND the SQL Query :

SELECT message.id, message.msg_body,message.creator_id,message_recipient.recipient_id
FROM message,message_recipient
WHERE message.creator_id='1' AND message_recipient.recipient_id='2'

the output is:

id msg_body    creator_id    recipient_id
1  "Hi user2"  1             2
2  "Hi user3"  1             2

Answer 1

You need to use JOIN to do that:-

SELECT message.id, message.msg_body,message.creator_id,message_recipient.recipient_id
FROM message JOIN message_recipient ON message.id = message_recipient.message_id
WHERE message.creator_id='1' AND message_recipient.recipient_id='2'

Note:- When you are trying to get data from two or more tables then you need to use different types of Joins.

Reference:- https://www.w3schools.com/sql/sql_join.asp