WHERE 子句中的 Mysql 和 if/else 语句

Question

我有这个 SQL 选择查询

SELECT * 
FROM bots 
WHERE id NOT IN (select botid 
                 from messages 
                 where messages.recipient = :recipient) 
limit 1

我需要做这样的事情:

SELECT * 
FROM bots 
[if isset(recipient = $recipient AND sender = $sender)] 
  WHERE id IN (select botid 
               from messages 
               where messages.recipient = :recipient and sender = :sender) 
else 
  WHERE id NOT IN (select botid 
                   from messages 
                   where messages.recipient = :recipient) 
limit 1

[] - 伪代码

如何查询？

机器人表:

-------------------------------------------------------
| id       |  auth_token   |  messages_today  | vkid  |
-------------------------------------------------------
| 1       |  token   |                    0   | 2323  |
-------------------------------------------------------
| 2       |  token   |                     0  | 2343  |
-------------------------------------------------------

消息表:

-----------------------------------------------------------
| id       |  recipient   |  sender  | botid  | messageid |
-----------------------------------------------------------
| 1        |  12          |        1  |     1  |      3322 |
-----------------------------------------------------------
| 2        |  12          |        2  |     2  |    332123 |
-----------------------------------------------------------
| 3        |  13          |        1  |     1  |    332123 |
-----------------------------------------------------------

Answer 1

使用外连接

• 返回机器人表中的所有机器人
• 和匹配的消息表中的数据
• 但随后我们消除了仅返回机器人的匹配数据对于该特定机器人不存在提供收件人和发件人的消息

返回所有机器人

Select * from bots B
LEFT JOIN messages M
  on M.BotID = B.ID
 and M.Sender = $sender
 and M.Recipient = $Recipient
Where M.BotID is null