GROUP_CONCAT(quantity ORDER BY ingredient SEPARATOR ',') AS quantities GROUP_CONCAT(ingredient ORDER BY ingredient SEPARATOR ',') AS ingredients, SUBSTRING_INDEX(SUBSTRING_INDEX(quantities, ',', 4), ',', -1) AS quantity_4 SUBSTRING_INDEX(SUBSTRING_INDEX(quantities, ',', 3), ',', -1) AS quantity_3, SUBSTRING_INDEX(SUBSTRING_INDEX(quantities, ',', 2), ',', -1) AS quantity_2, SUBSTRING_INDEX(SUBSTRING_INDEX(quantities, ',', 1), ',', -1) AS quantity_1, SUBSTRING_INDEX(SUBSTRING_INDEX(ingredients, ',', 4), ',', -1) AS ingredient_4, SUBSTRING_INDEX(SUBSTRING_INDEX(ingredients, ',', 3), ',', -1) AS ingredient_3, SUBSTRING_INDEX(SUBSTRING_INDEX(ingredients, ',', 2), ',', -1) AS ingredient_2, SUBSTRING_INDEX(SUBSTRING_INDEX(ingredients, ',', 1), ',', -1) AS ingredient_1, ), then you can start from a variation of this query, and decompose the grouped data using SUBSTRING_INDEX(str,delim,count): - Pivot If you want to have the list of ingredients as different columns (ingredient_1, quantity_1, ingredient_2, quantity_2. (Implicit JOINs are more difficult to read and error-prone, it's easy you miss one link-condition on the WHERE and you get a carteasian product you weren't expecting). Note that I have changed your implicit JOINs to explicit ones. :- | :-Īpple Pie | Apple 1 Cane Sugar 1 Goat Butter 1 Tabantha Wheat 1 This is going to give you a string with all the ingredients and corresponding quantities. JOIN ingredients ON ingredients.ingredientId = mealTotal.ingredientId GROUP_CONCAT(ingredient_and_quantity ORDER BY ingredient_and_quantity SEPARATOR '\t' ) AS ingredient_listĬONCAT(ingredient, ' ', quantity) AS ingredient_and_quantity So starting with the already existing query, you'd convert it to: - Concatenate all your (ingredient, quantity) You can use GROUP_CONCAT to return all the ingredients and quantities as a list.
0 kommentar(er)
