MySQL：AND运算符不起作用

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

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

You need to join them on message id. Try this:

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

I added this in where clause message.id = message_recipient.message_id in your code and it's works

we can able to join multiple table using where clause .

SELECT message.id, 
message.msg_body,message.creator_id,message_recipient.recipi‌​ent_id FROM 
message,message_recipient WHERE message.id = message_recipient.message_id and 
message.creator_id='1' AND message_recipient.recipient_id='2'