CODEWITHSUNDEEP
phpmysqljoinleft-join
Rosamunda
Rosamunda

Reputation: 14980

Why this mySQL LEFT JOIN won't work? Can it be the WHERE clause?

I'm making a PHP script to show to which user roles the current user belongs. The data is in mySQL and what I want is to show all available roles and of all of them, show where the user belongs to.

this is my roles table

CREATE TABLE roles(
    roleID int unsigned not null auto_increment primary key,
    nombreRol char(50) not null
)

The roles here are:

registered
pending
student
former-student

and this is the table that connects the usuarios table with the roles table

CREATE TABLE rolesUsuarios (
    rolesUsuariosID int unsigned not null auto_increment primary key,
    userID int not null,
    nombreRol char(50) not null
)

I'm trying to create a LEFT JOIN like this:

SELECT  
roles.nombreRol,
rolesUsuarios.userID
FROM roles 
LEFT JOIN rolesUsuarios
ON roles.nombreRol = rolesUsuarios.nombreRol
WHERE rolesUsuarios.userID = 183

(I'm not concerned with security at the moment, just checking that the query is working)

In the example, the user 183 has the roles registered and student.

The intended result should be like this:

nombreRol -------- userID
registered ------- 183
pending ---------- NULL
student ---------- 183
former-student --- NULL

But I get this result instead:

nombreRol -------- userID
registered ------- 183
student ---------- 183

How may I change the join? (I've followed the example here and it does work there, and I think that I've followed the logic of the query correctly)

Upvotes: 1

Views: 76

Answers (2)

Tung Thanh
Tung Thanh

Reputation: 36

I thinhk the result is correct. If you want to get your expected result, your WHERE condition should like that: WHERE rolesUsuarios.userID = 183 or rolesUsuarios.userID is NULL

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Your where clause is turning the left join into an inner join. Add it to the ON clause of the join instead.

Try this:

SELECT  
roles.nombreRol,
rolesUsuarios.userID
FROM roles 
LEFT JOIN rolesUsuarios
ON roles.nombreRol = rolesUsuarios.nombreRol
AND rolesUsuarios.userID = 183

Upvotes: 1

Related Questions