2

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.

Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
eshaa
  • 386
  • 2
  • 7
  • 26
  • 3
    Is normalising your original table an option? Because that should really be your first priority here... – Lukas Eder Dec 27 '13 at 07:29
  • 3
    @LukasEder is right. the solutions below use FIND_IN_SET() which will get the right result... eventually. But it cannot be indexed, so it's bound to do table-scans of *both* tables. If you have more than a few hundred rows of data, you won't be happy with the performance. – Bill Karwin Dec 27 '13 at 07:37

2 Answers2

2

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

valex
  • 23,966
  • 7
  • 43
  • 60
  • Hi @valex,thanks for the solution.I would be glad if i know how efficient the query works in terms of performance when have tons of records.Thanks again ! – eshaa Dec 27 '13 at 07:52
  • 1
    @user1650864 I agree with @BillKarwin and his comment to your question. Using this not normalised tables structure isn't performance friendly. You can't join these tables without a full table scan. Even if you have index on `EmpFriendsID` it can't be used. Instead you should create relation table for example `[EmpId,FriendId]` and instead of `EmpFriendsID="2,3"` you will get two rows in this table `((1,2)(1,3))`. In this case you can use indexes and this DB structure will be work much faster on tons of records. – valex Dec 27 '13 at 08:28
0

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
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83