How to SELECT multiple values from a row and join it as a single column value

Question

Hi this is my database structure and data, I want to get the following output as shown in the image below.

I have a section in the table where the foodtypes allow multiple value so i have made 2 tables, 1 table for the values(dd_foods) and 1 more for the section ID(restaurant_food). now i want to select those multiple row id values from restaurant_food.foodtype but as the name from dd_foods. How do i do that?

I'm not sure how to explain it here so i made an image sorry if it's confusing.

Use [GROUP_CONCAT](https://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) — Giorgos Betsos, Oct 17 '15 at 17:38

score 2 · Accepted Answer · answered Oct 17 '15 at 17:40

2

You can use the GROUP_CONCAT function.

SELECT r.id, r.name, GROUP_CONCAT(dd.foodtypes)
FROM restaurants r 
INNER JOIN restaurants_food rf on rf.restaurants_id = r.id 
INNER JOIN dd_foods dd on dd.id = rf.food_id
GROUP BY r.id, r.name

answered Oct 17 '15 at 17:40

eugenioy

11,825
28
35

To my way of thinking, and to a rough approximation, there is no problem in SQL for which GROUP_CONCAT is the solution - especially so when application-level code is available – Strawberry Oct 17 '15 at 18:31

score 1 · Answer 2 · answered Oct 17 '15 at 18:43

Just use something like...

SELECT r.*
     , f.* 
  FROM restaurants r 
  JOIN restaurant_foodtype rf 
    ON rf.restaurant_id = r.restaurant_id 
  JOIN foodtypes f 
    ON f.foodtype_id = rf.foodtype_id;

...and handle everything else in your application level code

score 0 · Answer 3 · answered Oct 17 '15 at 17:47

select id, concat(restaurant, "-", foodtype) as restaurant_info
from(
select r.id as id, CONCAT(r.id, "-" ,r.name, "-") as restaurant, group_concat(ddf.foodtypes  separator ',') as foodtype
from restaurants as r
inner join restaurant_food as rf on rf.restaurants_id = r.id
inner join dd_foods as ddf on ddf.id = rf.food_id
)

How to SELECT multiple values from a row and join it as a single column value

3 Answers3