Search code examples
mysqljoinanti-join

MySQL exclude ids using joins

I have 2 tables:

uid uname
1   alex
2   anna
3   sergey
4   arnold
5   john

mid message  uid
1   hello    3
2   DELETED  3
3   xcvcxv   4
4   bye      1
5   DELETED  2
6   4452     5

I would like to get all messages, but if message contains "DELETED", exclude this message' userID from all messages (after this message do not return messages from userID 3 and 2), using JOINs and without NOT IN. Thanks for the help.

Solution

  • This should select all messages of users that don't have DELETED messages:

       SELECT m.*
     FROM message m
LEFT JOIN message m2
       ON m2.uid = m.uid 
      AND m2.message = 'DELETED'
    WHERE m2.mid IS NULL;