mysql选择与特定记录相关的所有列

Question

假设我有以下表格:

食物

ID | title
---+----------
1  | sandwich
2  | spaghetti

成分

ID  | food_reference | type | location | bought
----+----------------+------+----------+----------
100 | 1              | ham  | storeA   | 11-1-2013
101 | 1              | jam  | storeB   | 11-1-2013
102 | 2              | tuna | storeB   | 11-6-2013

我想要一个选择查询，它将找到所有匹配的行，所需的输出:

{id:1,title:sandwich,ingridients:[
    {id:100,type:ham,location:storeA,bought:11-1-2013},
    {id:101,type:jam,location:storeB,bought:11-1-2013}],
id:2,title:spaghetti,ingridients:[
    {id:102,type:tuna,location:storeB,bought:11-6-2013}]}

到目前为止，我有这样的东西:

SELECT F.id, F.title, group_concat(I.d_id ,I.type,I.location,I.bought SEPARATOR ',') as ingridients,
FROM food F, ingridients I
WHERE F.id=I.food_reference
GROUP BY F.id

;

问题是，所有这些值都被连接起来(显然)导致了这个输出:

{id:1,title:sandwich,ingridients:100hamstoreA11-1-2013,101jamstoreB11-1-2013},
{id:2,title:spaghetti,ingridients:102tunastoreB11-6-2013}

注意:此输出是使用对象上的函数 json_encode 创建的

那么你有什么建议吗？

Answer 1

为什么不能用inner join，试试

 select * from food inner join Ingredients on food.id=Ingridients.food_reference;

然后尝试

SELECT foo.id, foo.title, 
group_concat(ing.d_id ,ing.type,ing.location,ing.bought   SEPARATOR ',') 
as ingredients,FROM food foo, ingridients ing   
WHERE foo.id=ing.food_reference;