How to correctly use LEFT JOIN IS NULL or NOT EXISTS in this query?

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

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.

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`

??

Answers


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

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

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.


Need Your Help

Best way to implement Java Observer pattern for complex properties

java observer-pattern

I'm trying to implement the Observer pattern using JDK Observer/Observable however I'm struggling to see the best way to use it on beans that contains beans as properties. Let me give you my concrete

How to handle text notifications in UIAutomation?

javascript ios objective-c notifications ui-automation

In iOS app,I am changing the text of the label.In Objective C,for text change there are delegates. How to handle the same scenario using UIAutomation?Are there any notifications, we get for the text