DanBarber DanBarber - 6 months ago 26
SQL Question

MYSQL: Right join not returning expected rows

I am trying to grab data from three different tables, and I am trying to display all the rows in the other tables by using the RIGHT JOIN however I don't seem to be returning them...

SELECT id, user_name, first_name, last_name FROM vtigercrm600.vtiger_users
RIGHT JOIN vtigercrm600.vtiger_users2group
on vtigercrm600.vtiger_users.id = vtigercrm600.vtiger_users2group.userid
RIGHT JOIN vtigercrm600.vtiger_groups
on vtigercrm600.vtiger_users2group.groupid = vtigercrm600.vtiger_groups.groupid
WHERE vtigercrm600.vtiger_users.id = '5'
group by vtiger_users.id;


I am getting the following from this

||ID||user_name||first_name||last_name||
||5||user1||john||smith||


But I was expecting data from the other tables such as

||ID||user_name||first_name||last_name||groupid||userid||groupname||description||
||5||user1||john||smith||14||5||Sales Dept||Sales Dept Group||


users table

||id||user_name||first_name||last_name||


users2group table

||groupid||userid||


groups table

||groupid||groupname||description||

Answer

When you use a right join, then the where clause should only contain conditions on the last table. However, I don't recommend using right joins. Most people find left join much easier to follow -- because it keeps all rows in the first table.

I think you should try using left join instead . . . with the tables in the same order. To get columns from other tables, you need to include the columns in the select.

I suspect it is the logic you want:

SELECT u.id, u.user_name, u.first_name, u.last_name, g.*
FROM vtigercrm600.vtiger_users u LEFT JOIN
    vtigercrm600.vtiger_users2group ug
    on u.id = ug.userid LEFT JOIN
    vtigercrm600.vtiger_groups g
    on ug.groupid = g.groupid
WHERE u.id = 5;

Notes:

  • Your query doesn't seem to need a group by. I don't see any aggregation.
  • If id is a number, then don't use single quotes. Only use single quotes for string and date constants.
  • The LEFT JOIN can probably be replaced by INNER JOIN -- although you will get no rows if the user is in no groups.
  • Table aliases make the query easier to write and to read.
  • You should get in the habit of qualifying all column names.
Comments