Need SQL Query

[Bola]

Hi there. I have table structure shown here:

category (Int - AutoIncrement) parent_category(Int) Title(string)
1 0
HOME PAGE
2 1
FIRST DEPTH 1
3 1
FIRST DEPTH 2
4 1
FIRST DEPTH 3
5 2
SECOND DEPTH 1
6 3
SECOND DEPTH 2
7 3
SECOND DEPTH 3
8 6
THIRD DEPTH 1

I want 1 (one) SQL query that can return me full depth for certain caregory.
In some meta-language
for category 8 it look like this:


SELECT * FROM table WHERE category = 8
WHILE parent_category <> 1
SELECT * FROM table WHERE category = [parent_category from previous
iteration]
WEND


So in that case resulting recordset would be

category (Int - AutoIncrement) parent_category(Int) Title(string)
8 6
HOME PAGE
6 3
FIRST DEPTH 1
3 1
FIRST DEPTH 2

I know that I can do this using more that one recordset, but I want all this
in one recordset.
I am using ADO, not ADO.NET

Thanks

 

[Michel Walsh]

Hi,


Someone can use nested sets.



Category Parent lft rgt ' Fields name
1 Null 1 16
2 1 2 6
3 1 6 13
4 1 14 15
5 2 3 4
6 3 7 10
7 3 11 12
8 6 8 9




So, to get anyone under a "parent", find its lft and rgt value and then:

SELECT Category
FROM nestedsets
WHERE lft BETWEEN parent.lft AND parent.rgt


to get the parent for a given "child", again, find the lft value for the
child then:

SELECT Category
FROM nestedsets
WHERE child.lft BETWEEN lft AND rgt

which, for child.lft =8, will return

1
3
6
8



Hoping it may help,
Vanderghast, Access MVP