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.

The *"correct"* approach is to fix the schema to be properly normalized. Do this correction once, then JOIN is fixed (and trivial and likely faster) forever more. To keep up with this denormalized approach (ick! ick!) see http://stackoverflow.com/questions/10895764/mysql-join-comma-separated-field?rq=1 and similar. — user2864740, Dec 23 '13 at 09:12
It really isn't a good idea to store comma separated values in a database column, because it gives you so many problems like this, and will impact performance because it can't be searched with an index; but the MySQL function [FIND_IN_SET()](http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_find-in-set) may help — Mark Baker, Dec 23 '13 at 09:13

Saharsh Shah · Accepted Answer · 2013-12-26T04:53:20.870

2

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 |

edited Dec 26 '13 at 04:53

answered Dec 23 '13 at 09:24

Saharsh Shah

28,687
8
48
83

This will not scale will. It's probably fine with 100 records, maybe even with 1000, but what about 100,000? The proper solution is to fix the schema to be in 1NF. It's especially bad because one column will be an integer, but the other must be a character type of some sort so there's also an implicit cast in there, and indexing will be of limited use, if any. – GordonM Dec 23 '13 at 09:25
i already use this function but it give same output ie only a single id. without normalization isn't there any solution ? – Tnd.exe Dec 23 '13 at 09:36
@Tnd.exe Please provide some sample data from both table and desired output. – Saharsh Shah Dec 23 '13 at 09:38
Table Restaurant name->abc cuisine_id->1,2,6,8 Table Cuisine cuisine_id->1 cuisine_name->Nepali ;cuisine_id->2 cuisine_name->Indian;cuisine_id->6 cuisine_name->Thai;cuisine_id->8 cuisine_name->Korean OUtput: name->abc cuisine_name->nepali -- it takes only single first id as FIND_IN_SET() function But i need name->abc cuisine_name-> nepali, Indian,Thai,Korean – Tnd.exe Dec 23 '13 at 10:34
@Tnd.exe Check my updated answer. You have to use `GROUP_CONCAT()` function – Saharsh Shah Dec 23 '13 at 10:45

score 1 · Answer 2 · answered Jan 21 '15 at 07:51

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 |

how to use join that pull comma seperated values from MySQL

2 Answers2