CODEWITHSUNDEEP
mysqlleft-join
codeDragon
codeDragon

Reputation: 565

LEFT JOIN returns null

I have following 2 tables, users and userRoles with following records in it:

userId  userName  firstName  lastName  password  image userRoles_roleId
........................................................................
1       [email protected]    A          A         1                1
2       [email protected]    B          B         2                2


roleId  userRole
.................
1       admin
2       user

With the following query this is what I get: 

SELECT users.userId, users.userName, users.firstName
     , users.lastName, users.password, users.image
     , userRoles.userRole FROM users 
LEFT JOIN userRoles ON users.userID = userRoles.roleId;

userId  userName  firstName  lastName  password image  userRole
................................................................
1       [email protected]    A          A            1            NULL
2       [email protected]     B          B            2            NULL

Of course what I would like to get is admin and user.

Am I am not understanding well what LEFT JOIN it supposed to do or what´s wrong?

Upvotes: 0

Views: 532

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35623

A left [outer] join will permit a row from the "right" table to be displayed even if there is no matching row in the "left" table. However if you don't choose the correct columns for the join then you won't get good results. In the users table there is a foreign key column and the name of that column indicates which table it relates to. That column is userRoles_roleId so you need to change the query to this:

SELECT users.userId, users.userName, users.firstName
     , users.lastName, users.password, users.image
     , userRoles.userRole 
FROM users 
LEFT JOIN userRoles ON users.userRoles_roleId = userRoles.roleId;

Upvotes: 2

Related Questions