在LEFT JOIN语句中使用CASE结果（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.

我试图根据CASE结果LEFT JOIN一个表。基本上,我想检查我是否是发件人,如果我不是,我想从用户数据库中检索发件人的名字。如果我是发件人,我想从users表中检索收件人名称。

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

这是我当前的数据库查询,这正确地提取信息,但users.username AS sendername将始终显示发件人名称:

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 = someid where someid is defined in the above case.

我想将其更改为以下等效项:LEFT JOIN用户ON users.id = someid其中someid在上述情况下定义。

Thanks!

1 个解决方案

#1

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.

您可能需要调整此SQL以获得您想要的内容,但其要点是连接两次,然后使用case语句从相应的表中提取所需的项目。