SQL Left Join 对每个表进行验证

Question

首先，我的SQL技能真的很差。这是我的表格:

Message :
id | title | arrayusers
1  |Title1 | a:2:{i:1;i:1;i:5;i:5;}
2  |Title2 | a:2:{i:1;i:1;i:5;i:5;}
3  |Title3 | a:2:{i:1;i:1;i:5;i:5;}
4  |Title4 | a:2:{i:1;i:1;i:5;i:5;}
5  |Title5 | a:2:{i:1;i:1;i:5;i:5;}
6  |Title6 | a:2:{i:1;i:1;i:5;i:5;}

Read :
id | status | userid | message_id
1  |   0    |   5    |    1
2  |   0    |   1    |    2
3  |   0    |   5    |    2
4  |   0    |   1    |    3
5  |   0    |   5    |    4
6  |   1    |   1    |    5
7  |   1    |   5    |    5
7  |   1    |   5    |    6

我使用用户 ID 1 进行测试:

我的目标是从 Message 获取所有行，其中我的用户 1 在 arrayusers 中，带有 LIKE(i:1;i:1;)

并且用户 1 不在表 Read (userid) 中，status 等于 0。如果另一个用户 (5) 而不是我(用户 1)在Read 表，我想查看我的Message 行

根据上面的数据，我想从 Message 表中返回 id 1/4

我用 Left join 发起了一个请求，但我的请求隐藏了行 ID 1 和 4，因为此表中还有另一个用户使用此 ID。

SELECT * FROM message as m
LEFT JOIN read as r ON m.id = r.message_id
WHERE m.arrayusers LIKE '%i:1;i:1;%'
AND r.id IS NULL

希望您理解我的要求。有人可以帮我调试我的请求吗？

谢谢

Answer 1

我会使用 NOT EXISTS 或 NOT IN 在 read 表中查找状态:

select * 
from message
where arrayusers like '%i:1;i:1;%'
and id not in
(
  select message_id
  from read
  where userid = 1
  and status = 0
);

关于您自己的查询:您只是缺少 read 表中的条件:

SELECT m.*
FROM message as m
LEFT JOIN read as r ON m.id = r.message_id and r.userid = 1 and r.status = 0
WHERE m.arrayusers LIKE '%i:1;i:1;%'
AND r.id IS NULL;