php - 查询多个表

Question

根据我的查询 yesterday ，我按如下方式重组了我的食谱数据库:

类别

cid | category_name
 1  | desserts
 2  | cakes
 3  | biscuits

食谱

id | recipe_name
1  | black forest cake
2  | angel cake
3  | melting moments
4  | croquembouche
5  | crepes suzette

成分

iid | ingredient_name
 1  | self-raising flour
 2  | milk
 3  | chocolate
 4  | baking powder
 5  | plain flour
 6  | eggs

食谱类别

recipe_id | category_id
   1      |    1
   4      |    1
   5      |    1
   1      |    2
   2      |    2
   3      |    3
   4      |    3

recipe_ingredients

recipe_id | ingredient_id
   1      |    1
   2      |    1
   4      |    1
   1      |    2
   2      |    2
   3      |    2
   5      |    2
   1      |    3
   2      |    3
   1      |    4
   3      |    5
   4      |    5

我的查询需要按类别返回食谱，并使用
列出成分以分隔成分(可能使用昨天建议的 GROUP_CONCAT(i.ingredient_name separator '
') 输出)。

因此，查询甜点将输出:

black forest cake:
self-raising flour
milk
chocolate

croquembouche:
self-raising flour
plain flour

crepes suzette:
milk
plain flour

我知道我必须加入 recipe_ingredients 和 recipes and ingredients and search categories，但我真的很难知道如何做到这一点。

Answer 1

查询:

SELECT A.recipe_name, GROUP_CONCAT(ingredient_name) AS ingredient_names
FROM recipes A
LEFT JOIN recipe_ingredients B ON A.id = B.recipe_id
LEFT JOIN ingredients C ON B.ingredient_id = C.iid
LEFT JOIN recipe_categories D ON A.id = D.recipe_id
LEFT JOIN categories E ON D.category_id = E.cid
WHERE category_id = <serach_id>
GROUP BY id

结果:

+-------------------+-------------------------------------------------+
| recipe_name       | ingredient_names                                |
+-------------------+-------------------------------------------------+
| black forest cake | chocolate,baking powder,self-raising flour,milk |
| angel cake        | self-raising flour,milk,chocolate               |
| melting moments   | milk,plain flour                                |
| croquembouche     | self-raising flour,plain flour                  |
| crepes suzette    | milk                                            |
+-------------------+-------------------------------------------------+

这是您使用 GROUP_CONCAT 要求的内容。每种成分由 ,

分隔