Querying Graph-like Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Querying Graph-like Data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-10-04 : 09:00:34

Jeremy writes "Just to let you know, I think that this question is insanely difficult ;)

SQL Server 7 SP3, Windows 2000 Server SP2

I have an online store app that contains categories for the products sold. Categories are structured by general/sub categories of indefinate length. Sub categories can be grouped into one or more general category.
Ex:
Category : Monitors
Category : 19 inch Monitors (sub category of monitors)
Category : Black Components
Category : Black Monitors (sub category of Black components)
Category : Black 19 inch Monitors (sub category of Black
Monitors and 19 inch Monitors)

I've modeled the data like this.

ProductCategories
-----------------
CategoryID
Name

ProductParentCategories
-----------------------
PPCID
CategoryID
ParentID

The model is rather simple, though I have found working with queries and deletes to be extremely difficult.

One such query: Get all of the available categories that can be assigned as a parent of a particular category (no circular references) This query is for editing the structure of the categories via a client app. Users need to be able to add parent categories to existing categories.

The beginning of such a query isn't too tuff.
SELECT *
FROM ProductCategories
WHERE CategoryID NOT IN
(SELECT ParentID
FROM ProductParentCategories
WHERE CategoryID = 16)

Here 16 is the categoryID of the current Category to get the available parents for. This query filters out the immediate parents of the Category.

My question is. How do I filter out all of the grandparents, great grandparents, etc? How do I filter out all of the descendants? I.E. If the data is structured like the first example I got, black components shouldn't be in the available parents list for black 19 inch monitors and black monitors shouldn't be an available parent for black components.

It's obvious that some kind of recursive mechanism needs to be in place, but that's all I can say about that. Just thinking about possible solutions makes my brain hurt. Maybe there is an easier way to model the data? There are are exact same problems with all other kind's of queries and deletes. Like deleting all children of a category. Repetative data would probably be easier to manage than this. This kind of stuff is basic computer science when working with custom ADTs in a lang like C, but SQL?eeeek!!

Jeremy Lowery"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources