我在数据库中运行此查询:
SELECT * FROM posts INNER JOIN profile ON posts.user_id = profile.user_id
WHERE posts.user_id IN
(SELECT user_id FROM users WHERE class_name IN
(SELECT class_name FROM classroom WHERE created_by = '456'))
OR posts.user_id IN
(SELECT user_id FROM users WHERE role = 'teacher' AND school_name = 'SK Taman Megah')
ORDER BY posts.created_at DESC;
这是我的教室桌子:
这是我的用户表:
这是我的帖子表:
这是我的资料表:
这是我的输出:
预期的输出应总共有4行,post_id为(60,57,61,56),但仅显示2行。 SQL查询中有错误吗?
最佳答案
我认为以下是您所期望的:
SELECT * FROM posts INNER JOIN profile ON posts.user_id = profile.user_id where posts.user_id IN (select distinct user_id from users left join classroom on classroom.class_name = users.class_name where classroom.created_by='456' or (users.role='teacher' and users.school_name='SK Taman Megah') )