MySQL exclude ids using joins

Question

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.

Please reword your question as either it's an easy usage of SELECT uid from table2 WHERE message <> 'DELETED'; or you should provide more edetails. What have you tried so far? — frlan, Dec 11 '15 at 08:49
SELECT * FROM table1 INNER JOIN Table2 ON table1.uid = table2.uid AND message != "DELETED' OR SELECT * FROM table1 INNER JOIN Table2 ON table1.uid = table2.uid WHERE message != "DELETED' This is an example though. If this is not the answer you are looking for then you should consider changing your question as stated above. — Mick, Dec 11 '15 at 08:58
I have tried solution by Mick, just added brackets after OR, but it errors with "Operand should contain 1 column(s)". But idea is correct, needed just change to tables. Thank you guys, problem is solved — Dmitry Chornobyl, Dec 11 '15 at 09:24

shmosel · Accepted Answer · 2015-12-11T09:30:27.037

0

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;

edited Dec 11 '15 at 09:30

answered Dec 11 '15 at 09:15

shmosel

49,289
6
73
138

MySQL exclude ids using joins

1 Answers1