mysql - SQL多对多查询返回false

Question

我正在尝试弄清楚如何进行多对多 sql 查询，但遇到了一个问题。

我的数据库结构是:

projects_users_roles: project_id, user_id, role_id
user_roles: id, name
projects: id, name, user_id

所以在我的查询中，我想选择 user_id=23 的所有项目以及他们在 projects_users_roles 中为该项目分配的角色名称

我以为我有它，但它返回 FALSE

$query = "
SELECT p.name AS project_name
, r.name AS role_name
FROM projects AS p
JOIN projects_users_roles AS a
   ON a.project_id = p.id
JOIN users_roles AS role
   ON role.id = a.role_id WHERE a.user_id = 23
";
$result = mysql_query($query);
var_dump($result);

Answer 1

难道别名 r 从未定义过？您的代码示例在 SELECT 中显示 r.name 而不是 role.name。

$query = "
SELECT p.name AS project_name
, role.name AS role_name
FROM projects AS p
JOIN projects_users_roles AS a
   ON a.project_id = p.id
JOIN users_roles AS role
   ON role.id = a.role_id WHERE a.user_id = 23
";
$result = mysql_query($query);
var_dump($result);