Mysql GROUP_CONCAT and IN query

Question

I have a table EMPDetails like

EmpID EmpName EmpFriendsID
1     Hari    2,3
2     Ramesh 
3     Suresh 

I would like to have a query to retrieve EmpFriends name if i give an EmpID.

example if EmpID 1 is provided,result should be

1     Hari    2,3     Ramesh,Suresh

Thanks.

Answer 1

To Join tables use FIND_IN_SET() and then group recors and use GROUP_CONCAT() to concatenate friends names

SELECT t.EmpID,t.EmpName,t.EmpFriendsID,
       GROUP_CONCAT(t1.EmpName)
FROM t
LEFT JOIN t as T1 on FIND_IN_SET(t1.EmpID,t.EmpFriendsID)
WHERE t.EmpID=1
GROUP BY (t.EmpID)

SQLFiddle demo

Answer 2

Use FIND_IN_SET() function

Try this:

SELECT E1.EmpID, E1.EmpName, GROUP_CONCAT(E2.EmpFriendsID)
FROM EMPDetails E1
LEFT JOIN EMPDetails E2 ON FIND_IN_SET(E2.EmpID, E1.EmpFriendsID)
GROUP BY E1.EmpID