CODEWITHSUNDEEP
phpmysql
Ivan Kurniawan Prasetyo
Ivan Kurniawan Prasetyo

Reputation: 43

How to select from table with one to many relation to be one row result

I have 2 tables:

Restaurant

+---------------+----------------+
| restaurant_id | Restaurant name|
+---------------+----------------+
| 1             | KFC            |
| 2             | McD            |
+----+---------------------------+

Cuisine

+---------------+---------------+----------------+
| cuisine_id    | restaurant_id | cuisine        |
+---------------+---------------+----------------+
| 1             | 1             | Fastfood       |
| 2             | 1             | Fried Chicken  |
| 3             | 2             | Fastfood       |
| 4             | 2             | Burger         |
+---------------+---------------+----------------+

Is it possible to get data like this = 

+---------------+----------------+------------------------+
| restaurant_id | Restaurant name| Cuisine                |
+---------------+----------------+------------------------+
| 1             | KFC            |Fastfood, Fried Chicken |
| 2             | McD            |Fastfood, Burger        |
+----+---------------------------+------------------------+

with a single query? Or should I do it via php (select table Restaurant first > foreach loop > select Cuisine by Restaurant id > parsing into new array).

Upvotes: 0

Views: 72

Answers (3)

Jeeva Balan
Jeeva Balan

Reputation: 393

SELECT Restaurant.restaurant_id, Restaurant.Restaurant_name, GROUP_CONCAT(Cuisine.cuisine)
FROM Restaurant
LEFT JOIN Cuisine ON Restaurant.restaurant_id = Cuisine.restaurant_id
GROUP BY Restaurant.restaurant_id, Restaurant.Restaurant_name

Upvotes: 0

devpro
devpro

Reputation: 16117

You can use GROUP_CONCAT:

SELECT c.restaurant_id,r.restaurant_name, GROUP_CONCAT(c.cuisine)
FROM Restaurant r
INNER JOIN Cuisine c ON c.restaurant_id = r.restaurant_id 
GROUP BY c.restaurant_id

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use GROUP_CONCAT for this:

SELECT r.restaurant_id, r.Restaurant_name,
       GROUP_CONCAT(c.cuisine ORDER BY c.cuisine)
FROM Restaurant AS r
LEFT JOIN Cuisine AS c ON r.restaurant_id = c.restaurant_id
GROUP BY  r.restaurant_id, r.Restaurant_name

Note: The ORDER BY clause within GROUP_CONCAT is only necessary if you want an ordered comma separated list of cuisine names.

Upvotes: 8

Related Questions