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