Categories and SQL statement to populate a dropdownlist?

Hi guys.. .this is an interesting problem.. I want to make a database table for a Categories type thing... well, simply put, i want one table, and with that, I can make categories, subcategories, sub sub categories, etc....


lets say i have the follow database:

VB Code:

ID                   ParentID                   SubLevel                   Text
1                     0                               0                            Automotive
2                     1                               1                            Sales
3                     2                               2                            New Cars
4                     2                               2                            Used Cars
5                     1                               1                            Service
6                     5                               2                            Oil Changes
7                     5                               2                            Mufflers

Eventually, i want to fill a dropdown list so it appears like this:

Automotive
Automotive | Sales
Automotive | Sales | New Cars
Automotive | Sales | Used Cars
Automotive | Service | Oil Changes
Automotive | Service | Mufflers

Those should all be items in a dropdownlist... This should be workable somehow with that database structure, i'm just unsure of how to go about a sql query that could fill a dataset with that structure... any ideas?

I would load those items in to an array, then recursively loop through the array, looking for all ID's that have a parent ID of the ID I am currently on, and then doing the same for them, etc.

As I find one, I would add it to the list, call the recursion, then delete it from the array I created.

Eventually, the array will have no elements and you'd be done.

I don't know if this will help but I came up with this solution for your query.

NOTE: I had to change the design of your table a little bit. This example I created in SQL Server.


--CREATE THE TABLE--
create table #cars
([id] int,
type_id int,
other_id int,
id_desc varchar(50)
)

go


insert into #cars
values(1,1,0,'Automotive')
insert into #cars
values(1,2,1,'Sales')
insert into #cars
values(1,3,1,'New Cars')
insert into #cars
values(2,3,1,'Used Cars')
insert into #cars
values(2,2,1,'Service')
insert into #cars
values(1,4,2,'Oil Changes')
insert into #cars
values(2,4,2,'Mufflers')

go



select m.id_desc as root, '' as parent, '' as child
from #cars m
where type_id = 1
union
select m.id_desc as root, n.id_desc as parent, '' as child
from #cars m
inner join #cars n on
m.[id] = n.other_id
where m.type_id = 1 and (n.type_id = 2 and n.[id] = 1)
union
select m.id_desc as root, n.id_desc as parent, o.id_desc as child
from #cars m
inner join #cars n on
m.[id] = n.other_id
inner join #cars o on
n.[id] = o.other_id
where m.type_id = 1 and (n.type_id = 2 and n.[id] = 1) and (o.type_id = 3)
union
select m.id_desc as root, n.id_desc as parent, o.id_desc as child
from #cars m
inner join #cars n on
m.[id] = n.other_id
inner join #cars o on
n.[id] = o.other_id
where m.type_id = 1 and (n.type_id = 2 and n.[id] = 2) and (o.type_id = 4)


this gives the result that you are looking for......


I hope this helps.