Need help with a Self Join

[wvmikep]

I think a self join is what I need.

I have a table with three columns:
CAT_ID int identity primary key
CAT_NAME varchar(20)
CAT_PARENT int

cat_parent is a primary key which references cat_id (yep, a self-referencing table). I'm doing this because I want to generate a crumb-trail kinda like Yahoo, where it shows the category you're in and then adds the subcategory the deeper you go. On the given page, I'll have the cat_id for the lowest subcategory. I need to get a list of the category names (cat_name) that complete the "path" from the main category to that lowest subcategory.

Can this be done via one SQL statement?

thanks ------------------------------------
"Unclean beast! Get thee down! Be thou consumed by the fires that made thee!" - Brother Jacobus in Dragonslayer

 

[ColinM]

Not that easy to do, usually have to use a cursor to iterate through them.

Have a look at this article

http://www.sqlteam.com/item.asp?ItemID=1602

 

[rajeevnandanmishra]

Hi,
If you had the top parent id and cat id as 1 and 1. Then the following procedure might be helpful for you.
You execute this procedure with the current page id and it will return you the full path starting from the top parent.

---------------------------------
create procedure retPath
(@cat_id int) as
declare @retPath varchar(255)
select @retPath = ''
while @cat_id>=1
begin
select @retPath=a.cat_name+'/'+@retPath, @cat_id=a.cat_id
from <myTable> a, <myTable> b
where a.cat_id=b.cat_parent and b.cat_id=@cat_id
if @cat_id=1
begin
select @retPath
return
end

end
select @retPath
return
-----------------------------

 

[seema3]

Hi wvmikep,
I need to do exactly what you have stated initially in the post abt creating a yahoo sort of trail.
Would like to know how u solved this problem.

I read the link provided by ColinM and I tried executing the procedure speified but I kept getting an error
abt Cursor does not exist.

My feeling is that in that particular proc provided in this article
Have a look at this article

http://www.sqlteam.com/item.asp?ItemID=1602

A cursor is opened, fetch a value, call the same proc again,so effectively isin't the cursor still open at this stage, shouldn't it be closed.

What is the best way to get this sort of hierarchy and how to display it on my page.

Thx in advance,
Seema