在我的MySQL数据库中,我有以下表格:
食谱(=ricette),其中包含食谱的ID和一些基本信息
成分(=ingredienti),该食谱中的成分行数与该食谱中的成分一样多,最后是桌子
recipe_kind (=tiporicetta),包含配方的 id 和配方的种类。
我想得到食谱的所有成分,所以我执行:
SELECT DISTINCT
r.nome,
r.descrizione,
r.persone,
t.tipo,
c.cibo,
i.quantita,
i.unitamisura_id
FROM
ricette AS r
LEFT JOIN
ingredienti AS i ON i.ricette_id = r.id
LEFT JOIN
cibo AS c ON c.id = i.cibo_id
LEFT JOIN
tiporicetta AS t ON t.id = r.tiporicetta_id
WHERE
(r.id = '52')
但是通过该查询,我多次获得名称,描述,人员和种类,每行一次。相反,我希望只有一次nome,descrizione,persone和tipo,然后是成分列表。如何使用唯一查询执行此操作?
试试这个:
SELECT
r.nome,
r.descrizione,
r.persone,
"", "", "", ""
FROM ricette AS r
WHERE
(r.id = '52')
UNION
SELECT
"", "", ""
t.tipo,
c.cibo,
i.quantita,
i.unitamisura_id
FROM
ricette AS r LEFT JOIN
ingredienti AS i ON i.ricette_id = r.id LEFT JOIN
cibo AS c ON c.id = i.cibo_id LEFT JOIN
tiporicetta AS t ON t.id = r.tiporicetta_id
WHERE
(r.id = '52')