mardi 20 octobre 2015

SQL Query, Group_Concat of multiple left joins and nested queries

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