MySQL 选择带有 NOT LIKE 的行

Question

这是我的表格的样子:

SELECT table2.status, table1.title 
FROM table1 RIGHT JOIN table2 ON table1.id = table2.id 

status    title
EXPIRED   USER A
EXPIRED   USER B
ACTIVE    USER B
EXPIRED   USER C

我想从状态不是 ACTIVE 的用户那里获取标题，我试过:

SELECT table2.status, table1.title 
FROM table1 RIGHT JOIN table2 ON table1.id = table2.id 
WHERE table2.status NOT LIKE 'ACTIVE'

status    title
EXPIRED   USER A
EXPIRED   USER B
EXPIRED   USER C

问题是用户 B 的状态是 ACTIVE 和 EXPIRED，我想要一个表向我展示表中所有没有 ACTIVE 状态的人:

status    title
EXPIRED   USER A
EXPIRED   USER C

Answer 1

如果您显示您的表结构，将会有所帮助。但这里有一种方法可以解决这个问题:

select t1.*
from table1 t1
where not exists (select 1
                  from table2 t2
                  where t2.id = t1.id and
                        t2.status = 'ACTiVE'
                 );