LEFT JOIN in MySQL across multiple tables with null values
I have the following table structure with data
TABLE: USER
USER ID | USER NAME
1 | Joe
2 | Mary
TABLE : USER GROUP
USER ID | GROUP ID
1 | 1
1 | 2
TABLE : GROUP
GROUP ID | GROUP NAME
1 | Company 1
2 | Company 2
TABLE : ROLE
ROLE ID | ROLE NAME
1 | Administrator
2 | Users
TABLE : USER ROLE
USER ID | ROLE ID
1 | 1
2 | 1
As you can see, user # 2 does not belong to any group. Roles and Groups are optional for me to the left, but when I run query like below
`SELECT a.user_id,
a.user_name
GROUP_CONCAT(r.role_name) AS role_names,
GROUP_CONCAT(g.group_name) AS group_names
FROM user a
LEFT JOIN role_map m ON a.user_id = m.user_id
INNER JOIN role r ON m.role_id = r.role_id
LEFT JOIN user_group s ON a.user_id = s.user_id
INNER JOIN group g ON s.group_id = g.group_id
GROUP BY a.user_id`
I am getting a cartesian product in a column role_names
- the result looks like this:
Joe | Administrators, Administrators | Company 1, Company 2
What am I doing wrong?
source to share
The easiest way to solve this is to use DISTINCT
in GROUP_CONCAT
( SQL Fiddle ) .Also , you need to add GROUP BY a.user_id
to group for each user:
SELECT a.user_id,
a.user_name,
GROUP_CONCAT(DISTINCT r.role_name) AS role_names,
GROUP_CONCAT(DISTINCT g.group_name) AS group_names
FROM `user` a
LEFT JOIN `user_role` m ON a.user_id = m.user_id
LEFT JOIN `role` r ON m.role_id = r.role_id
LEFT JOIN `user_group` s ON a.user_id = s.user_id
LEFT JOIN `group` g ON s.group_id = g.group_id
GROUP BY a.user_id;
source to share