开发者

SELECT DISTINCT not working after CASE statement

开发者 https://www.devze.com 2023-02-24 10:21 出处:网络
I\'m trying to return the first message between two users, whether I sent that message or received it.I\'ve coded the below query which returns all the correct threads, but seems to ignore the DISTINC

I'm trying to return the first message between two users, whether I sent that message or received it. I've coded the below query which returns all the correct threads, but seems to ignore the DISTINCT clause - and I end up with multiple results with the same 'someid'.

How do I correct this query so that there will be no duplicate values returned in the resultant 'someid' column?

Here's what I'm currently using:

SELECT DISTINCT CASE WHEN $userid != senderid THEN senderid ELSE GROUP_CONCAT(receivers.id SEPARATOR ', ') END someid,   
            CASE WHEN $userid != senderid THEN sen开发者_运维技巧ders.username ELSE GROUP_CONCAT(receivers.username SEPARATOR ', ') END somename,
            messages.body, 
            messages.time
    FROM messages 
    LEFT JOIN messages_recipients AS recipients ON messages.id = recipients.messageid
    LEFT JOIN users AS senders ON messages.senderid = senders.id
    LEFT JOIN users AS receivers ON recipients.userid = receivers.id
    WHERE recipients.userid = $userid
    OR messages.senderid = $userid
    GROUP BY messages.id
    ORDER BY messages.time DESC


As @Mitch Wheat mentioned, you probably have to separate the DISTINCT into an outer query.

SELECT DISTINCT * FROM 
    (
    SELECT CASE WHEN ... GROUP BY messages.id ORDER BY messages.time DESC
    ) AS inner_q
ORDER BY messages.time DESC

But remember, DISTINCT is for distinct records not a single value (someid). messages.time may preventing you from getting what you are after.

0

精彩评论

暂无评论...
验证码 换一张
取 消