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!


  `userId` int(6) unsigned NOT NULL,
  `username` varchar(200) NOT NULL,
  PRIMARY KEY (`userId`)
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`)

INSERT INTO `supervisors` (`supervisorId`, `userId`) VALUES
  ('1', '2'),
  ('1', '3'),
  ('1', '4'),
  ('3', '4');
  • Join one more users table joined by supervisorId and take name from this table copy for GROUP_CONCAT.
    – Akina
    Mar 21 at 8:42

1 Answer 1

SELECT u.username, 
       GROUP_CONCAT(su.username) AS supervisors 
FROM users AS u
LEFT JOIN supervisors AS s ON s.userid = u.userid 
LEFT JOIN users AS su ON s.supervisorid = su.userid 
GROUP BY u.userid;
  • huh. For some reason, I thought it wasn't allowed to use non primary keys of a joined table and it was going to need some sort of clever subselect or something to get the usernames! Mar 21 at 14:18
  • @BenHolness Joining condition is ANY expression. Even subquery! The only restriction - the output of this expression must be convertable to boolean (in MySQL this means "absolutely any"). Two columns values comparing produces boolean immediately despite of these columns are primary, referenced or something else.
    – Akina
    Mar 21 at 16:30
  • Oh I meant I thought that when there is a GROUP BY, I could only use primary keys for what I am SELECTing, in this case the GROUP_CONCAT. Maybe I'm conflating it with when there's a COUNT Mar 23 at 2:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.