Re: Parts explosion with repeated subtrees

Joe, it would appear to be much easier to use the following menu model. The SQL required to answer your question if the menu is modeled like this is rather elegant.

/* Menu code tested on SQL Server 2000 */ Create Table Menu
( iMenuId int not null,
  item varchar(25) not null,
  iParentId int not null,
  iLevelId int not null
)

INSERT INTO Menu select 1,'Eggs Benedict', 0, 1
INSERT INTO Menu select 2, 'English Muffin', 1, 2
INSERT INTO Menu select 3, 'Canadian Bacon', 1, 2
INSERT INTO Menu select 4, 'Egg', 1, 2
INSERT INTO Menu select 5, 'Béarnaise sauce', 1,2

INSERT INTO Menu select 6, 'Dill', 5, 3
INSERT INTO Menu select 7, 'Hollandaise sauce', 5, 3

INSERT INTO Menu select 8, 'Egg', 7,4
INSERT INTO Menu select 9, 'Lemon Juice', 7,4 INSERT INTO Menu select 10, 'Butter', 7, 4

/* All simple items used in construction of Eggs Benedict */

select distinct item from menu where iMenuId not in (select iParentId from menu)

/* All Child Items of Eggs Benedict that are recipes */

select item from menu where iMenuId in (select iParentId from menu) and iParentId <> 0

In the case of representing a full menu at a restaurant I would add one more
column called iTreeId int not null.

71062.1056_at_compuserve.com (--CELKO--) wrote in message news:<c0d87ec0.0212131601.71ea8ad0_at_posting.google.com>...
> Another tree oriented problem.
>
> Imagine that we have a forest of trees called "Menu" which stores the
> menu items for a restaurant. The first level under the root is the
> dishes served. Each dish is then decomposed into its ingredients; the
> ingredient can be either simple (salt, water, milk, flour, etc) or it
> can have a recipe of its own (Béarnaise sauce, Hollandaise sauce,
> etc.)
>
> Let me build a tree of each dish or recipe down to its most basic
> simple ingredients:
>
> CREATE TABLE Menu
> (dish_name VARCHAR (25) NOT NULL,
> food_item VARCHAR (25) NOT NULL,
> item_type CHAR (6) NOT NULL DEFAULT ‘simple'
> CHECK (item_type IN (‘dish', ‘recipe', ‘simple')),
> PRIMARY KEY (dish_name, food_item),
> << constraints >>);
>
> INSERT INTO Menu
> VALUES (‘Eggs Benedict', ‘English Muffin', ‘simple'),
> (‘Eggs Benedict', ‘Canadian Bacon', ‘simple'),
> (‘Eggs Benedict', ‘Egg', ‘simple'),
> (‘Eggs Benedict', ‘Béarnaise sauce', ‘recipe');
>
> INSERT INTO Menu
> VALUES (‘Béarnaise sauce', ‘Dill', ‘simple'),
> (‘Béarnaise sauce', ‘Hollandaise sauce', ‘recipe');
>
> INSERT INTO Menu
> VALUES (‘Hollandaise sauce', ‘Egg‘, ‘simple'),
> (‘Hollandaise sauce', ‘Lemon Juice‘, ‘simple'),
> (‘Hollandaise sauce', ‘Butter‘, ‘simple');
>
> If I put the Menu into a nested set model, I would want to build a
> full tree for each menu item. However, things like ‘Béarnaise sauce'
> and ‘Hollandaise sauce' are going to appear all over the place.
>
> I can recursively expand each ingredient which has a recipe of its own
> until I have a result set of only simple ingredients.
>
> But is there another model or better query which would allow us to
> find all the simple ingredients of a given dish?
Received on Tue Dec 17 2002 - 07:35:15 CET