SQL Query Help

Database recursive query needed

[loneregister]

I need some help setting up a query or a view that will give me quick results.

I have a table [Categories]
CategoryID
ParentID
Name
ProductID

If the productID is blank - it's a branch in the structure. If the productID is not blank, it's a leaf in the structure.

Sample Data:
1, 0, "Root Category", ""
2, 1, "Drills", ""
3, 1, "Saws", ""
4, 2, "", "Dewalt Drill 18V"
5, 2, "", "Dewalt Drill 14.4V"
6, 3, "", "Makita Saw 18V"
7, 3, "", "Makita Saw 14.4V"
8, 2, "Corded Drills", ""
9, 8, "", "Dewalt ScrewGun"

What I need is this - a query that will return all items that are a child of category 1, or all items that are a child of category 2 or 3

So, my first query - what are the children products of category 1 would return

Dewalt Drill 18V
Dewalt Drill 14.4V
Makita Saw 18V
Makita Saw 14.4V
Dewalt ScrewGun

A query on category 2 would return:
Dewalt Drill 18V
Dewalt Drill 14.4V
Dewalt ScrewGun

This is with Microsoft SQL.

Help!

Thanks all,
Kevin

[loneregister]

What I am after here is a query that will do the work of building I guess what is called an "adjacency" table and return those results.

However, what may be called for here, due to speed needs, is building that adjacency table and storing it as a totally separate table, and then using that for my queries as needed.

Kevin

[scintex]

Kevin,

I don't quite understand your example- to me the results are incorrect. Of course this could be me not getting it.

Anyhow, I would say in answer to:

"a query that will return all items that are a child of category 1"

Do:
select name from categories where parentid = 1

With your sample data this would return:

Drills
Saws

"A query on category 2 would return: "
Dewalt Drill 18V
Dewalt Drill 14.4V
Corded Drills

I would have thought that your fields are used for the following:

[Categories]
CategoryID - this ok
ParentID - if 0 it is top level, otherwise it's a child that links to categoryid
Name - ok
ProductID - ok too

Not quite sure if I've helped or not :)!

[FalseDawn]

No, Kevin's examples are correct. You are not taking into account the "recursive" nature of the query.

For all children of category 1, you need to "retrace back" through the parent IDs, to see if it is a child of a "node" with this category - in the case af category 1, this is the "root" node, for category 2, it is "drills".
Note how this includes "Corded Drills", as this is a leaf of "Drills"

I had to do something similar a long time ago, but I had to set a bound on the maximum depth of the tree and had to use many self-joins. The query was quite complex, and I remember thinking at the time I wish I had chosen a different table structure to represent the data.

There was a thread about representing hierarchical data posted recently - if possible, I would read it and consider implementing a different structure.

Edit: Ahh, I see you posted that thread, too!
SQL is really not meant to represent (and query from) hierarchical data structures - if it were me, I'd create a class wrapper for the table and implement member functions in code to retrieve the data you want - this could work pretty well as long as the table is not too large.

[edited by: FalseDawn at 5:47 pm (utc) on Aug. 17, 2006]