CODEWITHSUNDEEP
mysqlsqlrelational-database
Tommy Arnold
Tommy Arnold

Reputation: 3389

SQL many to many query returns false

I am trying to figure out how to do many to many sql queries and have hit an issue.

My db structure is:

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

So in my query I would like to select all projects for user_id=23 and what role name they have been assigned on that project in projects_users_roles

I thought I had it but its returning 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);

Upvotes: 2

Views: 169

Answers (2)

Lamak
Lamak

Reputation: 70638

You are using an alias that doesn't exists on your query, r:

SELECT  p.name AS project_name, 
        r.name AS role_name
FROM projects AS p
INNER JOIN projects_users_roles AS a
   ON a.project_id = p.id
INNER JOIN users_roles AS r
   ON r.id = a.role_id 
WHERE a.user_id = 23

And, maybe role is a reserved word on MySQL.

Upvotes: 1

hall.stephenk
hall.stephenk

Reputation: 1165

Could it be that the alias r is never defined? Your code sample shows r.name in the SELECT instead of 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);

Upvotes: 6

Related Questions