Walker Walker - 6 months ago 32
SQL Question

Returning distinct values in either column X or Y in MySQL

How do I select DISTINCT values from a combination of two columns? I am creating a threaded inbox, much like the iPhone SMS system and I want to do something along the lines of:

SELECT DISTINCT (senderid or recipientid - whichever value != my $userid),
name, body FROM messages
WHERE $userid = (either senderid, recipientid - whatever is discovered first).


Sorry that's so confusing, but the idea is to return the latest message (regardless of whether the message was sent or received) to create distinct threads in the messaging system. Only 1 thread can exist between two users.

EDIT: Here's the code I'm now using

// Pull all the girls who are being followed by $userid

$sql = "SELECT * FROM messages
LEFT JOIN (
SELECT DISTINCT CASE WHEN '$userid' != senderid
THEN senderid ELSE recipients
END otherid, users.username AS sendername, messages.body, messages.time
FROM messages
WHERE messages.recipients = '$userid' OR messages.senderid = '$userid'
) subq ON users.id = subq.otherid
ORDER BY subq.time";

Answer

MySQL has the case expression so you can try

SELECT DISTINCT CASE when $userid != senderid then senderid
                else recipientid end someid /*senderid or recipientid - whichever value != my $userid*/,
                name, body    
FROM messages
WHERE $userid = senderid
    or $userid = recipientid;

Select *
from messages
left join (SELECT DISTINCT CASE when $userid != senderid then senderid
                    else recipientid end someid /*senderid or recipientid - whichever value != my $userid*/,
                    name, body    
             FROM messages
             WHERE $userid = senderid
             or $userid = recipientid) subq
    on messages.userid = subq.someid;

Note that I haven't tested this syntaxt in MySQL - I don't have an instance to work on ...but using the column name someid shouldn't be a problem, since I made the query where it exists into a subquery of something else.