我在数据库中运行此查询:

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;


这是我的教室桌子:

mysql - Phpmyadmin未选择正确的值-LMLPHP

这是我的用户表:

mysql - Phpmyadmin未选择正确的值-LMLPHP

这是我的帖子表:

mysql - Phpmyadmin未选择正确的值-LMLPHP

这是我的资料表:

mysql - Phpmyadmin未选择正确的值-LMLPHP

这是我的输出:

mysql - Phpmyadmin未选择正确的值-LMLPHP

预期的输出应总共有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') )

10-08 03:58