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...

There are ways to do it, but all of them are rather complicated. The right solution would be to properly normalize your data, and store those `prefLocationId` values in a separate table (one value per row). That would make the query trivial. — Max Shawabkeh, Apr 16 '10 at 05:04

score 2 · Accepted Answer · answered Apr 16 '10 at 05:03

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

score 0 · Answer 2 · answered Apr 16 '10 at 05:28

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.

How to get this done in mysql?

2 Answers2