I'm playing around with sql, playing with tutorials on internet, some basic sqlite in an app and have come stuck with the following issue.
I have several tables described below.
Table 1 (Recipe)
ID, Name
1, Carrot Cake
2, Cheese Cake
3, Chocolate Cake
Table 2 (Methods)
ID, Method Name, RecipeID
1, Old School Method, 1
2, Low Salt Method, 1
3, Extra Chocolate, 3
Table 3 (Ingredients)
ID, Name, Quantity, MethodID
1, Eggs, 2, 1
2, Carrots, 1, 1
3, Flour, 40, 1
4, Salt, 2, 1
5, Eggs, 2, 2
6, Carrots, 1, 2
7, Flour, 50, 2
8, Milk, 20, 3
9, Chocolate, 10, 3
10, Eggs, 1, 3
What i basically want to do is print out a list of recipes, all possible methods, and all ingredients in those methods. If no indigents or methods listed, just show null or be blank.
Step 1:
SELECT Recipe.* FROM Recipe
obviously prints out
id name
1 Carrot Cake
2 Cheese Cake
3 Chocolate Cake
Step 2:
SELECT Recipe.*, GROUP_CONCAT(Method.Name) as Methods
FROM Recipe
LEFT JOIN Method
ON Method.RecipeID = Recipe.ID
GROUP BY Recipe.id
returns
id name Methods
1 Carrot Cake Low Salt Method,Old School Method
2 Cheese Cake (null)
3 Chocolate Cake Extra Chocolate
Which again makes sense, however now i want to list the ingredients and quantity in each method, something like
1 Carrot Cake Low Salt Method (Eggs 2, Carrots 1, Flour 40, Salt 2), Old School Method (.... )
The format hasn't got to be the same but just show same information, somehow
Having a quick google, i came across 'nested queries' however I'm struggling to make them work
Any guidance would be gratefully appreciated
Thanks
Aucun commentaire:
Enregistrer un commentaire