Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Search the initial parents from recursive SQL
Here's an answer (no thanks to me) from the Miicrosoft SQL server newsgroup
(Radovan Dobic). Note it assumes you the top of the chain has null parents
(you don't show those records in your question).
I write recursive function and then do query using function. Try this
CREATE TABLE [dbo].[Catalog] (
[Id] [int] IDENTITY (1, 1) NOT NULL , [ParentCatId] [int] NULL , [Name] [varchar] (50) COLLATE Croatian_CI_AS NULL) ON [PRIMARY]
insert into catalog (parent,name) values (NULL,'Level 1') insert into catalog (parent,name) values (1,'Level 1.1') insert into catalog (parent,name) values (1,'Level 1.2') insert into catalog (parent,name) values (1,'Level 1.3') insert into catalog (parent,name) values (2,'Level 1.1.1') insert into catalog (parent,name) values (2,'Level 1.1.2') insert into catalog (parent,name) values (3,'Level 1.2.1') insert into catalog (parent,name) values (6,'Level 1.1.2.1') insert into catalog (parent,name) values (NULL,'Level 2') insert into catalog (parent,name) values (9,'Level 2.1') insert into catalog (parent,name) values (9,'Level 2.1') insert into catalog (parent,name) values (11,'Level 2.2.1') insert into catalog (parent,name) values (11,'Level 2.2.2')
CREATE function fn_FindTop (@id int,@level int=null)
returns @rettables TABLE (id int,org_id int)
as
begin
declare @org_id int
Set @level=isnull(@level,0)+1
select @org_id=parentCatId from Catalog where id=@id
if isnull(@org_id,0)=0
insert into @rettables (org_id) values (@id) else
insert into @rettables (org_id) select org_id from
dbo.fn_FindTop(@org_id ,@level)
update @rettables set id=@id
return
end
and query can be like this:
select a.name,a.id,top_level=c.name,top_id=c.id
from catalog a inner join dbo.fn_findtop(8,0) b
on a.id=b.id
inner join catalog c
on b.org_id=c.id
I hope you can find some ideas from this
radovan
Isma <Ismael_at_NOSPAMiballo.com> wrote in message
news:9vscea$5ar$1_at_sunnews.cern.ch...
> HI,
>
> I've got a query I can't resolve.
>
> Exple
>
> parent child
> A B
>
> B C
>
> C E
>
> D F
>
> From E, is it possible by a query to wind up to A ?
> The same for F(which gives D) !!!
>
>
> Thanks
>
>
Received on Thu Dec 20 2001 - 09:30:01 CST