Getting distinct users in a messages database table

For a messages table with the following structure

id, sender_id, receiver_id, text

I want to build a list of distinct users (ordered by message id)开发者_如何学JAVA who have either sent a message to or received one from the current session holder. The list should contain a user only once.

A query such as:

"SELECT * FROM messages WHERE sender_id = '$sess_id' OR receiver_id = '$sess_id'";

yields duplicates i.e. multiple rows with the same user. Does one necessarily have to do the filtering after the SQL query or is there an SQL query that will yield the desired unique list?

Thanks!

You could do:

SELECT sender_id
FROM   messages
WHERE  receiver_id = '$sess_id'
UNION
SELECT receiver_id
FROM   messages
WHERE  sender_id = '$sess_id'

SELECT
  CASE receiver_id WHEN '$sess_id' THEN sender_id ELSE receiver_id END AS user_id
FROM messages
WHERE receiver_id = '$sess_id'
   OR sender_id = '$sess_id'
GROUP BY CASE receiver_id WHEN '$sess_id' THEN sender_id ELSE receiver_id END
ORDER BY MAX(message_id)

MySQL allows you to reference column aliases in the GROUP BY clause, so you could also simply do:

…
GROUP BY user_id
…

You could also use the distinct keyword:

SELECT distinct sender_id
FROM   messages
WHERE  receiver_id = '$sess_id'
UNION
SELECT distinct receiver_id
FROM   messages
WHERE  sender_id = '$sess_id'

More information can be found here:

http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html