43

I have two tables : DISH and DISH_HAS_DISHES. Dish table has all the dishes and "Dish_has_dishes" table has a one-to-many relationship with "Dish" table. I.e. a dish can have multiple dishes. For example

DISH : 

dish_id   dish_name
1         dish_1
2         dish_2
3         dish_3
4         dish_4

DISH_HAS_DISHES :

meal_id   dish_id
1         2
1         3
1         4

Here meal_id and dish_id both are IDs from DISH table. Now I want a format like this:

meal_id     dish_ids     dish_names
1           2,3,4        dish_2, dish_3, dish_4

That is comma separated dish id and names for each meal. How to do that?

Swar
  • 5,473
  • 3
  • 31
  • 43

1 Answers1

101

Use GROUP_CONCAT FUNCTION

http://dev.mysql.com/tech-resources/articles/4.1/grab-bag.html

 SELEct m.meal_Id, 
        GROUP_CONCAT(dish_id) dish_ids, 
        GROUP_CONCAT(dish_name) dish_names
 FROM DISH_HAS_DISHES m JOIN DISH d ON (m.dish_id = d.dish_id)
 GROUP BY meal_Id
Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59
  • Thats awesome. Than you Micheal for your quick reply. Just one thing to ask, I am getting "dish_ids" column as "BLOB" type. Rest are proper. Why? – Swar Aug 16 '10 at 11:31
  • @Swar, Should be varchar, what is length of dish_ids? Also look to this http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat. Check value of parameter group_concat_max_len, by default if resulted string will be more than 512 chars (default value) it will be returned as BLOB – Michael Pakhantsov Aug 16 '10 at 12:13
  • They are just integers with length 11. I tested with just 4 ids - 6,7,8 & 9. Though with blob type, its working perfectly but just a query. – Swar Aug 16 '10 at 12:17
  • @Swar, very strange. in doc: The return value is a nonbinary or binary string, depending on whether the arguments are nonbinary or binary strings. The result type is TEXT or BLOB unless group_concat_max_len is less than or equal to 512, in which case the result type is VARCHAR or VARBINARY. (Prior to MySQL 5.0.19, GROUP_CONCAT() returned TEXT or BLOB group_concat_max_len greater than 512 only if the query included an ORDER BY clause.) – Michael Pakhantsov Aug 16 '10 at 12:21
  • Somehow, blob type is coming only when the fields are INTEGERS. I tried with other fields also. – Swar Aug 16 '10 at 12:33
  • @Swar, try GROUP_CONCAT(trim(convert(dish_id, char(5)))) dish_ids, any be its help – Michael Pakhantsov Aug 16 '10 at 12:48
  • One possible modification, GROUP_CONCAT should have DISTINCT. SELECT m.meal_Id, GROUP_CONCAT(DISTINCT dish_id) dish_ids, GROUP_CONCAT(DISTINCT dish_name) dish_names FROM DISH_HAS_DISHES m JOIN DISH d ON (m.dish_id = d.dish_id) GROUP BY meal_Id – imnd_neel Jan 19 '17 at 06:26
  • 1
    Thanks @MichaelPakhantsov, You save lots of my time, Really appreciate it. – Udit Rawat Dec 04 '17 at 11:41