Using GROUP_CONCAT in MySQL »

If we have two tables with the following structure:

CREATE TABLE
	member(user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL);
INSERT INTO
	member (name)
VALUES
	('Vasim'),
	('Alice'),
	('Sumit'),
	('Sunil'),
	('Vipul'),
	('Pravin'),
	('Nikhil'),
	('Harshad');

CREATE TABLE
	friendships(friendship_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, initiator_id INT NOT NULL, reciprocator_id INT NOT NULL, UNIQUE(initiator_id,reciprocator_id));
INSERT INTO
	friendships
VALUES
	(1,1,2),
	(2,1,3),
	(5,1,6),
	(6,1,7),
	(9,2,1),
	(10,2,3),
	(13,2,4),
	(14,2,7),
	(17,2,8),
	(19,3,1),
	(20,3,2),
	(23,3,6),
	(24,3,8);

---

Then you can use ‘GROUP_CONCAT’ as following:

SELECT
	m.user_id, GROUP_CONCAT(f.reciprocator_id) as ids
FROM
	friendships f
JOIN
	member m
ON
	m.user_id = f.initiator_id
WHERE
	1=1
GROUP BY
	m.user_id;

And the result would be:

---

What if you want to select user names too instead of just IDs? you can do this as following:

SELECT
	m1.name, GROUP_CONCAT(m2.name) friends
FROM
	friendships f
LEFT JOIN
	member m1
ON
	m1.user_id = f.initiator_id
LEFT JOIN
	member m2
ON
	m2.user_id = f.reciprocator_id
GROUP BY
	name;

And the result would be: