php - 连接多个关系表

Question

我有一 table 食物

food_id     food_name  

  1           chappathi

和另一个表格类别

category_id    category_name

  1              non-veg
  2              nutrition

和一个关系表 food_category

food_id    category_id

  1          1
  1          2

其中food_id是food表中food_id的外键，category_id是category表中category_id的外键，会有一种食物没有分类的情况我试过了

 $sql=      "SELECT * FROM food f
            LEFT JOIN food_category fc
            ON f.food_id=fc.food_id
            LEFT JOIN category c
            ON fc.category_id=c.category_id"

它给了我不止一种食物，我想要的是

item name: chappathi
categories: non-veg, nutrition
------------------------------
second row of result set if there is any

Answer 1

试试这个:

SELECT f.food_name AS item_name,  GROUP_CONCAT(c.category_name SEPARATOR ', ') AS categories
FROM food f
LEFT JOIN food_category fc ON f.food_id=fc.food_id
LEFT JOIN category c ON fc.category_id=c.category_id
GROUP BY f.id

希望对你有所帮助。