CODEWITHSUNDEEP
databasepostgresql
Manaus
Manaus

Reputation: 451

Find records without join match

I have a table of mail recipients linked to a table of mail messages through a many2many table (recipients_mails). Every time I send a mail, I add a row to the many2many table.

I need to know what recipients did NOT receive email with id n. 3.

This is how the table is carved: 

Recipients
recipient_id, email

Recipients_mails
recipient_fk, mail_fk

Mails
mail_id, content etc.

Thank you very much

Upvotes: 1

Views: 35

Answers (1)

Quassnoi
Quassnoi

Reputation: 425371

SELECT  *
FROM    recipients
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    recipients_mails
        WHERE   (recipient_fk, mail_fk) = (recipient_id, 3)
        )

Upvotes: 1

Related Questions