Arg81
Well-known member
Hi,
I have a dataTable that needs to contain data columns from another table. To achieve this I'm using subselects.
I'm wondering whether there is an easier way to do this though.
I have 2 tables. "Ingredient" and "RecipeIngredient". RecipeIngredient is the table which splits up a many to many relationship, but it also includes a "weight" column, as the ingredient weight is different for each recipe.
My tables look like this:
Ingredient
IngredientID
IngredientName
IngredientCost
RecipeIngredient
RecipeID
IngredientID (both set as Primary Key)
IngredientWeight
My datatable merges the both, so I can use a grid to display all the data together. To achieve this, the SQL I use (inc. the subqueries) is:
This creates the dataTable to have;
RecipeID
IngredientID
IngredientWeight
IngredientName
IngredientCost
IngredientWeightMix (an expression column)
IngredientCostMix (an expression column)
This way, I don't have to load ALL ingredients (although there's only 134 atm) , as the grid will show the name from the above dataTable.
I'm just after some advice of the best way of getting the Name and Cost into the RecipeIngredient table. This now has over 1000 rows and is the "main" table to be filled with lots of rows (1 recipe has at least 5 ingredients). I'm worried that the subselects will take a long time to return once this table is filled with 1000's more rows.
Any advice appriciated
I have a dataTable that needs to contain data columns from another table. To achieve this I'm using subselects.
I'm wondering whether there is an easier way to do this though.
I have 2 tables. "Ingredient" and "RecipeIngredient". RecipeIngredient is the table which splits up a many to many relationship, but it also includes a "weight" column, as the ingredient weight is different for each recipe.
My tables look like this:
Ingredient
IngredientID
IngredientName
IngredientCost
RecipeIngredient
RecipeID
IngredientID (both set as Primary Key)
IngredientWeight
My datatable merges the both, so I can use a grid to display all the data together. To achieve this, the SQL I use (inc. the subqueries) is:
VB.NET:
SELECT RecipeID, IngredientID, IngredientWeight,
(SELECT IngredientName FROM Ingredient WHERE (IngredientID = RecipeIngredient.IngredientID)) AS IngredientName,
(SELECT IngredientCost FROM Ingredient AS Ingredient_2 WHERE (IngredientID = RecipeIngredient.IngredientID)) AS IngredientCost
FROM RecipeIngredient
This creates the dataTable to have;
RecipeID
IngredientID
IngredientWeight
IngredientName
IngredientCost
IngredientWeightMix (an expression column)
IngredientCostMix (an expression column)
This way, I don't have to load ALL ingredients (although there's only 134 atm) , as the grid will show the name from the above dataTable.
I'm just after some advice of the best way of getting the Name and Cost into the RecipeIngredient table. This now has over 1000 rows and is the "main" table to be filled with lots of rows (1 recipe has at least 5 ingredients). I'm worried that the subselects will take a long time to return once this table is filled with 1000's more rows.
Any advice appriciated