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.


    id, user_id, name

    id, message_id, label_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...

FROM labels l
INNER/LEFT JOIN messages_labels ml ON (
WHERE... user_id=:user_id ...

... and `message_id <> :message_id`



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

FROM labels l
LEFT JOIN message_labels ml
    ON = ml.label_id
    AND message_id = :message_id
WHERE l.user_id = :user_id

One method:

SELECT labels.*, count( AS mlid
FROM labels
JOIN messages_labels ON = messages_labels.label_id
WHERE (user_id = :user_id) AND (message_id = :message_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