I have two MySQL tables, users and supervisors. In this simplified example, users has just an ID and a Username, and supervisors is a table that links two users together.

I would like to construct a query that returns two columns - a username and a list of their supervisors' usernames.

I have worked out how to do it with just the IDs of the supervisors with this query:

SELECT U.username, GROUP_CONCAT(S.supervisorId) AS Supervisors FROM users AS U LEFT JOIN `supervisors` AS S ON S.userId=U.userID WHERE 1 GROUP BY U.userID;

Which returns 1,3 for Steve's supervisors. Instead, I would like it to return Carol, Dave (ideally in alphabetical order with a comma after the space, but I can deal with that later if necessary).

Is this possible? I'm not sure how to achive it. Thanks!

dbfiddle

CREATE TABLE IF NOT EXISTS `users` (
  `userId` int(6) unsigned NOT NULL,
  `username` varchar(200) NOT NULL,
  PRIMARY KEY (`userId`)
) DEFAULT CHARSET=utf8;
INSERT INTO `users` (`userId`, `username`) VALUES
  ('1', 'Dave'),
  ('2', 'Jane'),
  ('3', 'Carol'),
  ('4', 'Steve');
  
  CREATE TABLE IF NOT EXISTS `supervisors` (
  `supervisorId` int(6) unsigned NOT NULL,
  `userId` int(6) NOT NULL,
  PRIMARY KEY (`supervisorId`,`userId`)
) DEFAULT CHARSET=utf8;

INSERT INTO `supervisors` (`supervisorId`, `userId`) VALUES
  ('1', '2'),
  ('1', '3'),
  ('1', '4'),
  ('3', '4');

I have two MySQL tables, users and supervisors. In this simplified example, users has just an ID and a Username, and supervisors is a table that links two users together.

I would like to construct a query that returns two columns - a username and a list of their supervisors' usernames.

I have worked out how to do it with just the IDs of the supervisors with this query:

SELECT U.username, GROUP_CONCAT(S.supervisorId) AS Supervisors FROM users AS U LEFT JOIN `supervisors` AS S ON S.userId=U.userID WHERE 1 GROUP BY U.userID;

Which returns e.g. 1,3 for Steve's supervisors. Instead, I would like it to return Carol, Dave (ideally in alphabetical order with a comma after the space, but I can deal with that later if necessary).

Is this possible? I'm not sure how to achive it. Thanks!

dbfiddle

CREATE TABLE IF NOT EXISTS `users` (
  `userId` int(6) unsigned NOT NULL,
  `username` varchar(200) NOT NULL,
  PRIMARY KEY (`userId`)
) DEFAULT CHARSET=utf8;
INSERT INTO `users` (`userId`, `username`) VALUES
  ('1', 'Dave'),
  ('2', 'Jane'),
  ('3', 'Carol'),
  ('4', 'Steve');
  
  CREATE TABLE IF NOT EXISTS `supervisors` (
  `supervisorId` int(6) unsigned NOT NULL,
  `userId` int(6) NOT NULL,
  PRIMARY KEY (`supervisorId`,`userId`)
) DEFAULT CHARSET=utf8;

INSERT INTO `supervisors` (`supervisorId`, `userId`) VALUES
  ('1', '2'),
  ('1', '3'),
  ('1', '4'),
  ('3', '4');

I have two MySQL tables, users and supervisors. In this simplified example, users has just an ID and a Username, and supervisors is a table that links two users together.

I would like to construct a query that returns two columns - a username and a list of their supervisors' usernames.

I have worked out how to do it with just the IDs of the supervisors with this query:

SELECT U.username, GROUP_CONCAT(S.supervisorId) AS Supervisors FROM users AS U LEFT JOIN `supervisors` AS S ON S.userId=U.userID WHERE 1 GROUP BY U.userID;

Which returns something like 1,3 for the supervisors. I would like it to return Carol, Dave (ideally in alphabetical order with a comma after the space, but I can deal with that later if necessary).

Is this possible? I'm not sure how to achive it. Thanks!

dbfiddle

CREATE TABLE IF NOT EXISTS `users` (
  `userId` int(6) unsigned NOT NULL,
  `username` varchar(200) NOT NULL,
  PRIMARY KEY (`userId`)
) DEFAULT CHARSET=utf8;
INSERT INTO `users` (`userId`, `username`) VALUES
  ('1', 'Dave'),
  ('2', 'Jane'),
  ('3', 'Carol'),
  ('4', 'Steve');
  
  CREATE TABLE IF NOT EXISTS `supervisors` (
  `supervisorId` int(6) unsigned NOT NULL,
  `userId` int(6) NOT NULL,
  PRIMARY KEY (`supervisorId`,`userId`)
) DEFAULT CHARSET=utf8;

INSERT INTO `supervisors` (`supervisorId`, `userId`) VALUES
  ('1', '2'),
  ('1', '3'),
  ('1', '4'),
  ('3', '4');

I have two MySQL tables, users and supervisors. In this simplified example, users has just an ID and a Username, and supervisors is a table that links two users together.

I would like to construct a query that returns two columns - a username and a list of their supervisors' usernames.

I have worked out how to do it with just the IDs of the supervisors with this query:

SELECT U.username, GROUP_CONCAT(S.supervisorId) AS Supervisors FROM users AS U LEFT JOIN `supervisors` AS S ON S.userId=U.userID WHERE 1 GROUP BY U.userID;

Which returns 1,3 for Steve's supervisors. Instead, I would like it to return Carol, Dave (ideally in alphabetical order with a comma after the space, but I can deal with that later if necessary).

Is this possible? I'm not sure how to achive it. Thanks!

dbfiddle

CREATE TABLE IF NOT EXISTS `users` (
  `userId` int(6) unsigned NOT NULL,
  `username` varchar(200) NOT NULL,
  PRIMARY KEY (`userId`)
) DEFAULT CHARSET=utf8;
INSERT INTO `users` (`userId`, `username`) VALUES
  ('1', 'Dave'),
  ('2', 'Jane'),
  ('3', 'Carol'),
  ('4', 'Steve');
  
  CREATE TABLE IF NOT EXISTS `supervisors` (
  `supervisorId` int(6) unsigned NOT NULL,
  `userId` int(6) NOT NULL,
  PRIMARY KEY (`supervisorId`,`userId`)
) DEFAULT CHARSET=utf8;

INSERT INTO `supervisors` (`supervisorId`, `userId`) VALUES
  ('1', '2'),
  ('1', '3'),
  ('1', '4'),
  ('3', '4');