1. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662

My turn:

I have 2 tables:

t1 (f1 int PK)
t2 (f1 int, L1 int)

t2.f1 is an FK to t1.f1

t2.L1 may contain the value that exists in t1.f1.

The task is to retrieve the entire chain.

t1:

f1
---
1
2
3
4
5

t2:

f1 L1
--- ---
1 2
2 3
3 4
4 5

The result should have:

Parent Child Level
------- ------ -------
1 2 1
2 3 2
3 4 3
etc...

Of course the actual structure is differernt, but the concept is the same. The customer wants to have the flexibility to either supply t1.f1 value, or retrieve parent/child/level info for ALL.

Any idea on how to accomplish it in one SELECT????

2. Registered User
Join Date
Aug 2003
Location
Delft, The Netherlands (EU)
Posts
447

## Re: Recursive SELECT...HOWTO

I don't have the answer, but I recognize your problem. Actually, you don't need table t1, but it's the recursive structure of t2, which forms the problem. I know that this is a standard problem in DWH projects, and the standard answer is to transform the recursive structure in a more flat form, including the level. I've never seen doing a query this task, but (of course) recursive procedures.

So, I would search in making a recursive stored procedure returning your recordset.

3. Registered User
Join Date
Oct 2001
Location
England
Posts
426
I suspect the only way to do this in a single select is to use a function. Any type of recursion would hit the 32 nest level limit.

You would implement a loop in the functionreturniong a table probably.

This is an SP to do a similar thing with a loop

http://www.nigelrivett.net/RetrieveTreeHierarchy.html

4. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
A function called by a select statement is the way to go. I use a recursive method similar to nigelrivett's, which I detailed here:

http://dbforums.com/t901390.html

...the difference being that nigelrivetts returns circular references, while mine (using the exists criteria) filters out circular references making the ID field unique in the result set. Which to use depends on the output you want.

blindman

5. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
Unfortunately I don't know how to write functions...in 7.0

I'll try OPENROWSET, since I love it so much

6. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
After long hours of negotiations and a full functional test of major processes the business agreed to convert the app to 2K. NO MORE OPENROWSET!!! I wrote it in 2 functions.

GO

if exists (select * from sysobjects where id = object_id('dbo.tblLink_ProfileItems') and sysstat & 0xf = 3)
GO

ProfileID int NOT NULL ,
ProfileItemNumber int IDENTITY (1, 1) NOT NULL ,
ItemTypeId int NOT NULL ,
FOREIGN KEY
(
ProfileID
) REFERENCES dbo.tblMainProfiles (
ProfileID
)
)
GO

GO

if exists (select * from sysobjects where id = object_id('dbo.tblMainProfiles') and sysstat & 0xf = 3)
drop table dbo.tblMainProfiles
GO

CREATE TABLE dbo.tblMainProfiles (
ProfileID int IDENTITY (1, 1) NOT NULL ,
ProfileName varchar (50) NOT NULL ,
ProfileVersion varchar (10) NULL ,
ProfileShortName varchar (50) NOT NULL ,
ProfileStatus int NOT NULL ,
ProfileType int NOT NULL ,
ProfileComplexityID int NOT NULL ,
ProfileLineageID int NOT NULL ,
PotentialBCProfile bit NOT NULL ,
CONSTRAINT PK_dbo_tblMainProfiles
PRIMARY KEY NONCLUSTERED (ProfileID)
)
GO

create function dbo.fn_Profile_Parents (
@ProfileID int = null)
returns @tbl table (
RecID int identity(1,1) not null ,
ParentID int not null ,
ChildID int null,
Seq int not null)
as begin
insert @tbl (ParentID, ChildID, Seq)
select top 100 percent
Parent = ProfileID, Child = ProfileID, 0
from tblMainProfiles m (nolock)
where exists (
select * from tblLink_ProfileItems i (nolock)
where m.ProfileID = i.ProfileID)
and ProfileID = @ProfileID
return
end
go

alter function dbo.fn_Profile_Children (
@ParentID int = null)
returns @tbl table (
RecID int identity(1000000,1) not null ,
ParentID int not null,
ChildID int null,
Seq int not null )
as begin
declare @i int
set @i = 1
insert @tbl (ParentID, ChildID, Seq)
where i.ProfileID = @ParentID and ItemTypeID = 2
while @@rowcount > 0 begin
set @i = @i + 1
insert @tbl (ParentID, ChildID, Seq)
select t.ChildID, ItemLinkID, @i from @tbl t
left outer join tblLink_ProfileItems i (nolock)
on t.ChildID = i.ProfileID and ItemTypeID = 2
left outer join @tbl t1
on ( t.ChildID = t1.ParentID)
where t1.ParentID is null
and t.ChildID is not null
end
return
end
go

The call that returns the resultset is as follows:

select ParentID, ChildID, Seq from fn_Profile_Parents(1435)
union
select ParentID, ChildID, Seq from fn_Profile_Children(1435)
order by 3, 2 asc

...and the result is:

ParentID ChildID Seq
----------- ----------- -----------
1435 1435 0
1435 1344 1
1435 1383 1
1383 1210 2
1383 1384 2
1344 1396 2
1344 1507 2
1384 NULL 3
1507 NULL 3
1396 1445 3
1210 1526 3
1445 NULL 4
1526 NULL 4

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•