Mysql连接多个表，同时填充左侧并检查特定行是否为NOT NULL

Question

我希望连接具有以下结构的多个表:

Users (userId, name)
Emoticons (emoticonId, name)
UnlockedEmoticons (userId, emoticonId, name)

对于每个项目，我希望显示用户是否解锁了特定表情符号，例如:

1(emoticonId), emoticonA(name), unlocked (userId IS NOT NULL) AS has?,
2(emoticonId), emoticonB(name), locked (userId IS NOT NULL) AS has?

问题是，如果未锁定的表情符号中没有 userId，我会得到空结果。

SELECT e.*, (ue.id IS NOT NULL) AS `has`
FROM UnlockedEmoticons ue RIGHT JOIN emoticons e ON ue.emoticonId = e.emoticonId
LEFT JOIN users u ON u.id = ue.userId
WHERE uk.userId = 33

我尝试更改连接语句顺序但无济于事。有办法实现吗？

我使用的是mysql 5.7

Answer 1

诀窍是在 ON 语句之后包含 AND:

FROM UnlockedEmoticons ue RIGHT JOIN emoticons e ON ue.emoticonId = e.emoticonId AND ue.userId = #{userId}

并删除 WHERE 语句。 :)