Presents your 
SQL SERVER E-NEWSLETTER for April 13, 2004

<------------------------------------------->
 
Working with relational division
 
Let's conduct an exercise in the operation called relational division, 
which is the opposite of relational multiplication. For this exercise, 
we'll use the following database: 
 
* RECIPES: A list of recipes 
 
* INGREDIENTS: A list of ingredients 
 
* RECIPEINGREDIENTS: An associative/bridge table that stands between 
Recipes and Ingredients, with a foreign key into each and a 
Quantity-required column 
 
* PANTRY: A list of the ingredients you actually have on hand; it also 
has a Quantity column. (You should assume that the Pantry table is up 
to date.) 
 
NOTE: Listing A below provides the SQL necessary to create the tables 
above. You can populate them with sample data. 
 
Now we'll try to determine what recipes you can make, given the pantry 
and its contents. A recipe has R ingredients, and a pantry has P 
ingredients. We have to locate where every ingredient in the recipe is 
in the pantry. These are the recipes that we can make. If your pantry 
lacks one ingredient in a recipe, you cannot make the recipe. 
 
In reality, there is more to the problem than this; in a word, 
quantities. For instance, a recipe may call for a dash of salt, but how 
do you determine the quantity of a dash? (This concept is beyond the 
scope of this tip.) So, for the sake of simplicity, let's assume that 
if you have any amount of ingredient X, you have enough. You can 
probably guess that the solution rests on a SQL statement's HAVING 
clause. 
 
Here is the query that determines which recipes you can make: 
 
SELECT dbo.Recipes.RecipeName, 
    COUNT(dbo.RecipeIngredients.IngredientID) AS IngredientCount, 
    COUNT(dbo.Pantry.IngredientID) AS PantryCount 
FROM dbo.Recipes 
    INNER JOIN 
    dbo.RecipeIngredients ON dbo.Recipes.RecipeID = 
 dbo.RecipeIngredients.RecipeID 
    LEFT OUTER JOIN 
    dbo.Pantry ON dbo.RecipeIngredients.IngredientID = 
dbo.Pantry.IngredientID 
GROUP BY dbo.Recipes.RecipeName 
HAVING COUNT(dbo.Pantry.IngredientID) >= 
COUNT(dbo.RecipeIngredients.IngredientID) 
 
As this example illustrates, there is considerable power in the HAVING 
predicate. The solution isn't always obvious, but some careful thought 
at the logical level before you write the SQL can clarify the problem 
and make the SQL straightforward to write. 
 
LISTING A: 
 
CREATE TABLE [dbo].[Ingredients] 
( 
    [IngredientID] [int] IDENTITY (1, 1) NOT NULL , 
    [Ingredient] [varchar] (50)  NULL 
) ON [PRIMARY] 
GO 
 
CREATE TABLE [dbo].[Pantry] ( 
    [PantryID] [int] IDENTITY (1, 1) NOT NULL , 
    [IngredientID] [int] NULL 
) ON [PRIMARY] 
GO 
 
CREATE TABLE [dbo].[RecipeIngredients] ( 
    [RecipeIngredientID] [int] IDENTITY (1, 1) NOT NULL , 
    [RecipeID] [int] NULL , 
    [IngredientID] [int] NULL , 
    [Quantity] [float] NULL , 
    [Comments] [ntext]  NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
GO 
 
CREATE TABLE [dbo].[Recipes] ( 
    [RecipeID] [int] IDENTITY (1, 1) NOT NULL , 
    [RecipeName] [varchar] (50)  NULL , 
    [RecipeDescription] [varchar] (255)  NULL , 
    [Source] [varchar] (50)  NULL , 
    [WhichMeal] [varchar] (50)  NULL , 
    [FoodCategoryID] [int] NULL , 
    [Vegetarian] [bit] NULL , 
    [TimeToPrepare] [varchar] (50)  NULL , 
    [NumberofServings] [smallint] NULL , 
    [CaloriesPerServing] [smallint] NULL , 
    [NutritionalInformation] [ntext]  NULL , 
    [Ingredients] [ntext]  NULL , 
    [Instructions] [ntext]  NULL , 
    [Notes] [ntext]  NULL , 
    [Utensils] [varchar] (255)  NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
GO 
 
CREATE TABLE [dbo].[Food Categories] ( 
    [FoodCategoryID] [int] IDENTITY (1, 1) NOT NULL , 
    [FoodCategory] [varchar] (50)  NULL 
) ON [PRIMARY] 
GO 
 
Arthur Fuller has been developing database applications for more than 
20 years. He frequently works with Access ADPs, Microsoft SQL 2000, 
MySQL, and .NET. 

----------------------------------------