mysql - SQL:多个连接和 WHERE 语句

Question

我有一个名为“users”的表，一个名为“modules”的表和一个名为“users_modules”的表。

“users”包含“user_id”、“first_name”、“last_name”和“role”。

'modules' 包含 'module_code' 和 'module_title'。

'users_modules' 包含 'user_id' 和 'module_code'

“用户”表中的“角色”列指定该用户是“学生”、“讲师”还是“管理员”。

我想向用户显示他们正在学习哪些模块，包括模块代码、模块标题以及该模块的讲师姓名。

我已经设法纠正了以下语句，它为我提供了某个用户正在学习的所有模块的“module_code”和“module_title”。

SELECT users_modules.user_id,
       users_modules.module_code,
       modules.module_title
  FROM users_modules
 INNER JOIN modules
    ON users_modules.module_code = modules.module_code
 WHERE user_id = 2;

我现在需要显示该模块注册了哪些讲师。需要成为上述语句的一部分，因为我在 PHP while 循环中使用它来生成 HTML 表。我知道我需要为用户表做另一个 JOIN，这样我就可以得到讲师的名字，但不确定如何完成代码的编写。

代码必须检查哪些用户注册了该模块(使用 users_module 表)，然后查看其中是否有任何用户具有等于“讲师”或“角色”(用户表中的列)管理员。

---

我想要什么的概述:

显示学生(在本例中为 user_id=2 的学生)他们注册了哪些模块以及教授该模块的讲师的结果。

参与此模块的每个人(教师或学生)都将在“users_modules”中有一个记录。

我需要一种方法来从“users_modules”表中提取每个模块的讲师，这是通过查看用户表中的“role”列来完成的。

Answer 1

假设您的用户表有一个“user_id”字段，下面应该可以满足您的需求。另请注意，在查询中为表添加别名始终是一个好习惯。

        SELECT u1.user_id AS 'Student ID', um1.module_code, m1.module_title,
        GROUP_CONCAT(u2.first_name) AS 'Lecturer First Name', 
        GROUP_CONCAT(u2.last_name) AS 'Lecturer Last Name'        
        FROM users u1 INNER JOIN users_modules um1 ON u1.user_id = um1.user_id
        INNER JOIN modules m1 ON um1.module_code = m1.module_code
        INNER JOIN users_modules um2 ON m1.module_code = um2.module_code
        INNER JOIN users u2 ON um2.user_id = u2.user_id
        WHERE u1.user_id = 2
        AND (u2.role = 'Lecturer' or u2.role = 'Admin')
        GROUP BY u1.user_id, um1.module_code, m1.module_title
        ;