How to avoid duplicates on friends
, I still get two bob
instead of just one bob
My table setup:
CREATE TABLE users( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) ); INSERT INTO users (id, name) VALUES (1, "Gregor"), (2, "Liza"), (3, "Matt"), (4, "Tim"), (5, "Lance"), (6, "Bob"); CREATE TABLE committee( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, friend_id INT, member_id INT, FOREIGN KEY (`user_id`) REFERENCES `users` (`id`), FOREIGN KEY (`friend_id`) REFERENCES `users` (`id`), FOREIGN KEY (`member_id`) REFERENCES `users` (`id`) ); INSERT INTO committee (user_id, friend_id, member_id) VALUES (3, 5, 1), (4, 5, 1), (3, 6, 2), (3, 6, 2), (4, 6, 2);
The query I used:
SELECT DISTINCT u.name, GROUP_CONCAT(f.name) AS friends FROM committee c INNER JOIN users u ON (u.id = c.user_id) INNER JOIN committee c2 ON c2.user_id = c.user_id INNER JOIN users AS f ON (f.id = c2.friend_id) WHERE (c.member_id = 1) GROUP BY u.id;
Current results:
name friends Matt Lance,Bob,Bob Tim Lance,Bob
My expectations:
name friends Matt Lance,Bob Tim Lance,Bob
Your u.name is different from f.name
Try this
You just need to use
DISTINCT
inGROUP_CONCAT()
:Please note that
SELECT DISTINCT ...
does not make sense in your query because you are usingGROUP BY
which returns different rows for each user.ViewDemo.