How to get this done in mysql?

Question

Consider i have a registartion table and there is field prefLocationId and it contains value like this 1,2,3,2,1,4 and so many.... And i have a table prefLocation which looks like this

Id LocationName
1  Chennai
2  Mumbai
3  Kolkatta
4  Delhi

and i want to select record of users and show values like

Chennai,Mumbai,Kolkatta,Mumbai,Chennai,Delhi and so on...

Answer 1

It must be table prefLocationId, not field and then you can select something like

SELECT pref.*,group_concat(prefLocation.name) 
FROM pref, prefLocationId, prefLocation 
WHERE pref.LocationId=prefLocationId.pref and prefLocation.id=prefLocationId.location
GROUP BY pref.id

Answer 2

Answered in this StackOverflow post...

Answer 3

I almost don't want to do this but there is an answer. Only, its about the worst possible thing I could imagine doing. You should really consider Col. Shrapnel's answer and split your values off into a separate table. But for the sake of completeness:

  SELECT registration.*, GROUP_CONCAT(prefLocation.name) AS prefLocationNames
    FROM registration
    JOIN prefLocation ON FIND_IN_SET(prefLocation.Id, registration.prefLocationId) > 0
GROUP BY registration.id

This will be VERY slow in comparison to what you would get if you split registration.prefLocationId because there's absolutely no way for this to use any indexes.