mySQL joining data from a diffrent table

Question

Alright, I'm pulling a list of users from a mySQl database then I'm looking for group assignment in a different table. if they are assigned to multiple groups multiple rows are returned as they should.

My question is how can I concat the group names into one column of the results.

  SELECT `u`.`ID`
         ,CONCAT(`First-Name`," ",`Last-Name`) as `Name`
         ,`g`.`Group-Name`
    FROM `application-users` AS `u`
    JOIN `groups` AS `g` ON (`g`.`Assigned-Users` LIKE CONCAT("%|",`u`.`ID`,"|%") )
   WHERE `u`.`Status` = "Active" && `u`.`Type` = "Business Development" 
ORDER BY `First-Name` ASC

Thanks in advance!

[Possible Duplicate](http://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql) — jeffjenx, Sep 19 '13 at 14:57
Thanks to all for adjusting / optimizing aspects of this post! — Cybervanes, Sep 19 '13 at 15:04

score 0 · Accepted Answer · answered Sep 19 '13 at 15:22

SELECT `u`.`ID`,CONCAT(`First-Name`," ",`Last-Name`) as `Name`,
GROUP_CONCAT(`g`.`Group-Name` SEPARATOR ', ') as `Groups`,
CONCAT('|',GROUP_CONCAT(`g`.`ID` SEPARATOR '|'),'|') as `GroupIDs`
FROM `application-users` AS `u`
JOIN `groups` AS `g` ON (`g`.`Assigned-Users` LIKE CONCAT("%|",`u`.`ID`,"|%"))
WHERE `u`.`Status` = "Active" && `u`.`Type` = "Business Development" 
GROUP BY `u`.`ID`

Thanks for your time peeps!

mySQL joining data from a diffrent table

1 Answers1