Michael Gulik Michael Gulik - 2 years ago 68
SQL Question

Simple sql query does not return corresponding value needed

I have two tables, one called users, other is messages in a single mysql database.

I am trying to make a "thread" that is between 2 users by selecting the message and name, along with the sender/user id's for extra information.

Users looks similar to this:

_id, Name
1 Mike
2 Chris
3 Agnes
.. ....

Messages is similar to this:

_id, sent_id, user_id, message
1 1 2 Hello
2 2 1 Sup
3 3 2 Goodbye
.. ...

The current Query is this:

SELECT users.name, messages.message, messages.user_id as user, messages.sent_id as sender
FROM messages, users
WHERE users.user_id = messages.user_id
AND (messages.user_id = $senderID AND messages.sent_id = $userID)
OR (messages.user_id = $userID AND messages.sent_id = $senderID);

It works, as it sends the correct messages; however, it returns all of the user names, not the ones associated with the message.

Answer Source

You need to add a line to filter the users:

AND users.user_id in ($userID, $senderID)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download