SQL Query - subselects

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
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:
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 :)
 

InertiaM

Well-known member
Joined
Nov 3, 2007
Messages
663
Location
Kent, UK
Programming Experience
10+
I'm confused as to why you need to subquery this. Surely INNER JOINs would be fine.

VB.NET:
SELECT
  RecipeIngredient.RecipeID,
  RecipeIngredient.IngredientID,
  RecipeIngredient.IngredientWeight, 
  Ingredient.IngredientName,
  Ingredient.IngredientCost
FROM
  RecipeIngredient INNER JOIN Ingredient ON RecipeIngredient.IngredientID = Ingredient.IngredientID

This will only load the Ingredients if they are used in RecipeIngredients.
 

InertiaM

Well-known member
Joined
Nov 3, 2007
Messages
663
Location
Kent, UK
Programming Experience
10+
It's all about personal preference and design.

If I design a system like this, I create a "log table" so I can track changes, user names etc. So rather than use the datatable to update, I run proper UPDATE commands and also INSERT commands to the "log table". I can then see who updated what and when :D
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
see, the way it is set up, is:

(a) user adds a new recipe. (this is updated to the recipe table).
(b) user then adds ingredients to that recipe (RecipeIngredients is a "child" of Recipe)

^^ at stage (b), is where the update to this dataTable takes place.

That's the only time the dataTable is updated. No user can remove rows in the application.
All other forms just view the data.
 
Top Bottom