Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Search the initial parents from recursive SQL

Re: Search the initial parents from recursive SQL

From: Jon Waterhouse <jonwaterhouse_at_mail.gov.nf.ca>
Date: Thu, 20 Dec 2001 12:00:01 -0330
Message-ID: <3c2203a6.0@209.128.1.3>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US