CODEWITHSUNDEEP
mysqlsql
user3434701
user3434701

Reputation: 29

How to do a "where" clause on data from join

I am trying to do an SQL query to get all the messages sent between 2 users: the "Sender" and the "Reciever" (yes I know this is spelled wrong) are foreign keys to the Users table.

I want to get back all the messages that the current user sent or received along with the username of the other person involved in the message.

I have the JOIN working (if I delete the where clause I get all messages), however I can't figure out how to restrict it using the where.

mysql_query(sprintf("
SELECT m.Content, us.Name Send, ur.Name Rec, m.Timestamp
FROM Messages m
JOIN Users us ON us.UserID = m.Sender
JOIN Users ur ON ur.UserID = m.Reciever
WHERE Send = '%s' or Rec = '%s'
ORDER BY TIMESTAMP DESC
"), $user, $user);

Upvotes: 0

Views: 65

Answers (2)

Rahul
Rahul

Reputation: 77866

You are almost there, just change it slightly like below

SELECT m.Content, us.Name as Send,
ur.Name as Rec, 
m.Timestamp
FROM Messages m
JOIN Users us ON us.UserID = m.Sender
JOIN Users ur ON ur.UserID = m.Reciever
WHERE us.Name = '%s' or ur.Name = '%s' <-- Change Here
ORDER BY TIMESTAMP DESC

Upvotes: 0

Evan Mulawski
Evan Mulawski

Reputation: 55334

You cannot use a column alias in the WHERE clause. See this SO question.

Your WHERE clause should be:

WHERE us.Name = '%s' OR ur.Name = '%s'

Upvotes: 2

Related Questions