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

Question

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);

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 us.Name = '%s' or ur.Name = '%s' ORDER BY TIMESTAMP DESC — cyadvert, Mar 18 '14 at 19:22
Strawberry, I am not clear on what you are suggesting. cyadvert, I dont think that worked, I get no results — user3434701, Mar 18 '14 at 19:25
@cyadvert I think the OP meant `LIKE` and not `=` where the wildcard `%` is used. — user2989408, Mar 18 '14 at 19:27
@user2989408: looks like OP meant an equality comparison. That "`%`" character isn't a SQL wildcard. The "`%s`" is a tag for sprintf replacement. — spencer7593, Mar 18 '14 at 19:34

score 2 · Answer 1 · edited May 23 '17 at 11:57

2

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'

edited May 23 '17 at 11:57

Community

1
1

answered Mar 18 '14 at 19:27

Evan Mulawski

54,662
15
117
144

score 0 · Answer 2 · answered Mar 18 '14 at 19:28

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

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

2 Answers2