DanBarber DanBarber - 1 year ago 109
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


But I was expecting data from the other tables such as

||5||user1||john||smith||14||5||Sales Dept||Sales Dept Group||

users table


users2group table


groups table


Answer Source

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;


  • 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.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download