我有这个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
[]-伪码
如何进行此查询?
Bots表:
-------------------------------------------------------
| 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 |
-----------------------------------------------------------
使用外部联接,这将
- 返回bots表中的所有bots
- 以及来自消息talbe的匹配数据
- 但我们消除了匹配的数据,只返回机器人其中为该特定机器人提供的具有收件人和发件人的消息不存在
返回所有机器人
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