CODEWITHSUNDEEP
mysql
DinhNgocHien
DinhNgocHien

Reputation: 717

Select distinct didn't work correctly (MySQL)

I have a query like that:

SELECT DISTINCT r.name AS role, r.id AS role_id, u.id AS user_id, concat( u.firstname, '', u.lastname ) AS name
FROM users u
INNER JOIN members m ON u.id = m.user_id
INNER JOIN member_roles mr ON m.id = mr.member_id
INNER JOIN roles r ON r.id = mr.role_id
WHERE m.project_id =11

My goal is to get unique u.id(user_id). Unfortunately, the result show two duplicate u.id from this multiple join. If I remove the last one join(INNER JOIN roles r ON r.id = mr.role_id), this query run correctly. But I didn't know why it was wrong if I add this line to my query. The last one join line is necessary to get the role's name, so I can ignore it. Can someone tell me where I did incorrectly in my query?

Upvotes: 0

Views: 76

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You are missing the fact that users can have multiple roles on a project. Fortunately, MySQL has group_concat() which can bring the multiple values together:

SELECT u.id AS user_id, concat( u.firstname, '', u.lastname ) AS name,
       GROUP_CONCAT(r.name) as roles,
       GROUP_CONCAT(r.id) as role_ids
FROM users u INNER JOIN
     members m
     ON u.id = m.user_id INNER JOIN
     member_roles mr ON m.id = mr.member_id INNER JOIN
     roles r
     ON r.id = mr.role_id
WHERE m.project_id = 11
GROUP BY u.id;

Upvotes: 1

Gunaseelan
Gunaseelan

Reputation: 2542

Use GROUP BY in your query to get rows with unique user_id.

Then the updated query will be look like the following.

SELECT DISTINCT r.name AS role, r.id AS role_id, u.id AS user_id, 
concat( u.firstname, '', u.lastname ) AS name
FROM users u
INNER JOIN members m ON u.id = m.user_id
INNER JOIN member_roles mr ON m.id = mr.member_id
INNER JOIN roles r ON r.id = mr.role_id
WHERE m.project_id =11 GROUP BY user_id

Upvotes: 1

Related Questions