Help needed with this complex query

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)

Help needed with this complex query

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-09 : 09:51:36

Hi
I wonder if you could help me with this query. I have been trying to write it for some time, but still cannot get the right solution.

I have following tables:

Products (productID,....)
Product_Attribute (productID, attributeID...)
Categories (categoryID...)
Product_Categories (productid, categoryid)
Category_Relations (parentCategoryID, childCategoryID)

Now I want to write a SP that takes 2 arguments: parentCategoryID & attributeID .

I want to get all the productIDs that come under the parentCategoryID.

So for instance, if I have following data:


Category_Relations:

Cat1    Cat2
Cat1    Cat3
Cat2    Cat4


Product_Categories:

Cat2    Prod2
Cat3    Prod3
Cat4    Prod4

For the above data, for 'Cat1' my SP should return Prod2 , Prod3 & Prod4 . Prod4 should be returned because it comes under Cat4, which is a child category of Cat2.

I have written a simple query that returns the products for only one level:

SELECT p.ProductID, p.ProductName, cat.CategoryID
FROM Products p INNER JOIN
Products_Categories cat ON p.ProductID = cat.ProductID INNER JOIN
Category_Relations cr ON cat.CategoryID = cr.ChildCategoryID
WHERE (cr.ParentCategoryID = 75)
ORDER BY p.ProductID

However, for 'Cat1', this query does not return products for Cat4 (which is a child category for Cat2)

Would be grateful if u can show me how I can achieve this.

Kind regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 09:57:59

See if this helps
http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-09 : 10:00:54

Here is an article I wrote on this subject, similiar to nigels. By reviewing both you should get a clear understanding of how to proceed.
----------------------------------------------------------------------
The most flexible and robust method of storing hierarchical data in a database is to use a table with a recursive relationship. In this design, each record has an associated parent record ID that indicates its relative place in the hierarchy. Here is an example:

CREATE TABLE [YourTable]
([RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentID] [int] NULL)

The challenge is to find a way to return all the child records and descendants for any given parent record.

While recursion is supported within SQL Server, it is limited to 32 nested levels and it tends to be ineffecient because it does not take full advantage of SQL Server's set-based operations.

A better algorithm is a method I call the "Accumulator Table".

In this method, a temporary table is declared that accumulates the result set. The table is seeded with the initial key of the parent record, and then a loop is entered which inserts the immediate descendants of all the records accumulated so far which have not already been added to the table.

Here is some skeleton code to show how it works:

--This variable will hold the parent record ID who's children we want to find.
declare @RecordID int
set @RecordID = 13

--This table will accumulate our output set.
declare @RecordList table (RecordID int)

--Seed the table with the @RecordID value, assuming it exists in the database.
insert into @RecordList (RecordID)
select RecordID
from YourTable
where YourTable.RecordID = @RecordID

--Add new child records until exhausted.
while @@RowCount > 0
insert into @RecordList (RecordID)
select YourTable.RecordID
from YourTable
inner join @RecordList RecordList on YourTable.ParentID = RecordList.RecordID
where not exists (select * from @RecordList CurrentRecords where CurrentRecords.RecordID = YourTable.RecordID)

--Return the result set
select RecordID
from @RecordList

This method is both flexible and efficient, and the concept is adaptable to other hierarchical data challenges.

For a completely different method of storing and manipulating hierarchical data, check out Celko's Nested Set model, which stores relationships as loops of records.

http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=145525%5D

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-09 : 11:45:03

thanks blindman, your solution worked

i_love_techno
Starting Member

4 Posts

Posted - 2006-02-09 : 18:20:56

quote:
Originally posted by madhivanan

See if this helps
http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

I have to agree, implementing a heirarchical tree is both a great and efficient approach when dealing with heirarchical data. We had a heirarchical tree implemented in our DB, courtesy of previous SQL dev. It used triggers to update the left and right node values upon an insert, and then it was as simple as selecting betwen the left and right node values of the particular parent node to get at the children. Slows down inserts, but definitely adds a speed boost to any querying as it requires only one scan.

Find happiness in listening to your discontent.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources