how to use join that pull comma seperated values from MySQL

Question

i have tables tbl_restaurant(name,cuisine_id,....) and tbl_cuisine(cuisine_id,cuisine_name)

In tbl_restaurant, cuisine_id field contain comma(,) separated values like (3,6,9,20,31) and when i try to pull cuisine_name using join it only takes first id (i.e: 3) in above case. I had a query:

SELECT tbl_restaurant.name, tbl_restaurant.cuisine_id, tbl_cuisine.cuisine_id,
tbl_cuisine.cuisine_name
FROM tbl_restaurant
LEFT JOIN tbl_cuisine ON tbl_restaurant.cuisine_id = tbl_cuisine.cuisine_id
WHERE tbl_restaurant.published =1
AND tbl_restaurant.id =$id

shall i parse before using join or else? any suggestion please.

Answer 1

You can use FIND_IN_SET() function to join two tables.

Try this:

SELECT r.name, r.cuisine_id, GROUP_CONCAT(c.cuisine_name)
FROM tbl_restaurant r
LEFT JOIN tbl_cuisine c ON FIND_IN_SET(c.cuisine_id, r.cuisine_id)
WHERE r.published =1 AND r.id = $id
GROUP BY r.id;

Check the SQL FIDDLE DEMO

OUTPUT

| NAME | CUISINE_ID | GROUP_CONCAT(C.CUISINE_NAME) |
|------|------------|------------------------------|
|  abc |    1,2,6,8 |    Nepali,Indian,Thai,Korean |

Answer 2

Use FIND_IN_SET and GROUP_CONCAT

SELECT r.name, r.cuisine_id, GROUP_CONCAT(c.cuisine_name)
FROM tbl_restaurant r
LEFT JOIN tbl_cuisine c ON FIND_IN_SET(c.cuisine_id, r.cuisine_id)
WHERE r.published =1 AND r.id = $id
GROUP BY r.id;

Output:

| NAME | CUISINE_ID | GROUP_CONCAT(C.CUISINE_NAME) |
|------|------------|------------------------------|
|  abc |    1,2,6,8 |    Nepali,Indian,Thai,Korean |