开发者

Using a CASE result in a LEFT JOIN statement (MySQL)

I'm trying to LEFT JOIN a table based on a CASE result. Basically, I want to check if I'm the sender, and if I'm not I want to retrieve the sender's name from the users database. If I AM the sender, I want to retrieve the recipients name from the users table.

Here's my current database query, this correctly pulls the information but the users.username AS sendername will always display the senders name:

SELECT DISTINCT CASE WHEN $userid != senderid THEN senderid ELSE recipients END someid,   
                users.username AS sendername, 
                messages.body, 
                messages.time 
FROM messages 
LEFT JOIN users ON users.id = messages.senderid 
WHERE messages.recipients = $userid 
OR messages.senderid = $userid 
ORDER BY messages.time

I'd like to change this to the equivalent of the following: LEFT JOIN users ON users.id = som开发者_运维百科eid where someid is defined in the above case.

Thanks!


SELECT DISTINCT CASE WHEN $userid != senderid THEN senderid ELSE recipients END someid,   
                CASE WHEN $userid != senderid THEN senders.senderid ELSE receivers.recipients END somename
                messages.body, 
                messages.time 
FROM messages 
LEFT JOIN users AS senders ON messages.senderid = senders.id
LEFT JOIN users AS receivers ON messages.recipients = receivers.id
WHERE messages.recipients = $userid 
OR messages.senderid = $userid 
ORDER BY messages.time

You may need to tweak this SQL to get exactly what you want, but the gist of it is to join twice, then use a case statement to pull the item you want from the appropriate table.

0

上一篇:

下一篇:

精彩评论

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

最新问答

问答排行榜