Recursive SQL? Im stuck!

[Guest_imported]

I have a DB table (category) which contains three columns (name, id, and parentId). If I had the following three records.
bedroom,1,null
bed,2,1
sheet,3,2

I would like to write some sql that could print out the following.

bedroom
bedroom>bed
bedroom>bed>sheet

I have no idea how to do this... could someone lend a helping hand?

thanks... if this isnt the right forum could you point me in the right direction.

 

[bitbrain]

The trick is to join the table to itself.

Let's assume that your table is named Item and has the following column names:

Desc, PK, FK

Your SQL would look something like this:

SELECT Item.Desc, Item_1.Desc, Item_2.Desc
FROM (Item LEFT JOIN Item AS Item_1 ON Item.PK = Item_1.FK)
LEFT JOIN Item AS Item_2 ON Item_1.PK = Item_2.FK

 

[Guest_imported]

I was really looking for a recursive solution...

If I had the following records:

bedroom,1,null
bed,2,1
sheet,3,2
lint,4,3

it wouldnt work with the solution given since it only handles things 3 layers deep... I need a solution that doesnt need to know the depth
thanks though...
-john

 

[MikeJones]

I'm not sure this is solvable in standard SQL as I don't think it has the ability to describe "How to" resolve something. The best I can come up with is to use a user defined function to resolve it. This will be RDBMS specific but in Oracle it would take the form of a stored function.

It could then look like this.

CREATE OR REPLACE FUNCTION recursive (i_item_pk in integer) return varchar2 is
--
l_return_string varchar2(1000);
--
cursor c_get_items is
select desc from item
connect by prior pk = fk
start with pk = i_item_pk;
--
begin
for l_recs in c_get_items_loop
l_return_string := l_return_string || l_recs.desc || '>'
end loop;
return l_string;
end;


In your sql you could then do
select recursive(pk)
from desc

and you should the the results you want.

HTH,

Mike.

 

[Guest_imported]

Thanks....
Well at least I can stop looking through SQL books trying to solve this.

Would anyone happen to know if this can be done with Access?And if so... how.

Or perhaps suggest a different table structure to model a tree? (one which I could use SQL to produce a listing of all elements)

thanks.
-jmirick