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.
精彩评论