如何正确使用左连接为NULL或不存在于此查询中?

After a few dozens of tries I still got wrong results, so I thought I'd better ask for help.

经过几次尝试，我还是得到了错误的结果，所以我想我最好寻求帮助。

Tables:

表:

labels
    id, user_id, name

messages_labels
    id, message_id, label_id

labels.id refers to message_labels.label_id

标签。id是指message_labels.label_id

How to get the correct results unused labels given a message-id and a user_id? By unused labels I mean labels that do not have an entry in message_labels for a given message-id, basically that only select labels to add to the message that are not in use for this message yet.

如何在给定消息id和user_id的情况下获得正确的结果未使用的标签?对于未使用的标签，我指的是在message_label中没有给定消息id条目的标签，基本上就是只选择标签添加到尚未用于此消息的消息中。

This means something like...

这意味着像……

SELECT l.id, l.name
FROM labels l
INNER/LEFT JOIN messages_labels ml ON (l.id=ml.label_id)
WHERE... user_id=:user_id ...

... and `message_id <> :message_id`

? ?

2 个解决方案

#1

This should work: LEFT JOIN on the label_id and the message id, anything without an ML record is what you want

这应该是可行的:在label_id和消息id上的左连接，任何没有ML记录的东西都是您想要的

SELECT 
    l.id, l.name
FROM labels l
LEFT JOIN message_labels ml
    ON l.id = ml.label_id
    AND message_id = :message_id
WHERE l.user_id = :user_id
AND ml.id IS NULL

#2

One method:

一个方法:

SELECT labels.*, count(messages_labels.id) AS mlid
FROM labels
JOIN messages_labels ON labels.id = messages_labels.label_id
WHERE (user_id = :user_id) AND (message_id = :message_id)
GROUP BY labels.id
HAVING (mlid = 0)

if I'm readin your question correctly.

如果我没看错你的问题。

#1