Mysql GROUP_CONCAT return only one value-CodePudding

I try to return how many friends a user have through GROUP_CONCAT But i only get Lance and not also Bob and it seems if i remove the WHERE condition. It works fine, but i would like to keep it. Since i want get all rows which are connected to member

CREATE TABLE users(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

INSERT INTO users (name)
VALUES ("Gregor"),
    ("Liza"),
    ("Matt"),
    ("Tim"),
    ("Lance"),
    ("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),
(4, 6, 2);

Here is the query:

SELECT u.name,
       GROUP_CONCAT(f.name) AS friends
FROM committee c
INNER JOIN users u ON (u.id = c.user_id)
INNER JOIN users AS f ON (f.id = c.friend_id)
WHERE (c.member_id = 1)
GROUP BY u.id;

What i get now:

name    friends
Matt    Lance
Tim Lance

What i expect:

name    friends
Matt    Lance,Bob
Tim Lance,Bob

CodePudding user response：

You need another join with committee to find all the other committees that Matt and Tim are on, so you can find their friends from those committees.

SELECT 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;

DEMO