Depth Travesal

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 375,195 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,099 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser:

Thread Tools Display Modes

Views: 810 | Replies: 2

•

Join Date: Mar 2008

Posts: 2

Reputation: snow00 is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

snow00

Offline

Newbie Poster

Depth Travesal

Mar 17th, 2008

Hi, I am a newbie for MSSQL. Previously, I was given a question regarding MSSQL. Although the interview had over, but I am curious to find out on how to solve the question below:

There are 2 tables, NAMES (ID INT IDENTITY(1,1), NAME SYSNAME) and
RELATIONSHIPS (NAMEID INT, PARENT_NAMEID INT) linked via NAMES.ID=
RELANTIONSHIP.NAMEID and where top-most name has a PARENT_NAMEID=0.
Show a nested list of names including LEVEL, NAMEID and NAME, where
LEVEL indicates the nest level (or depth) from top. You may use functions,
stored procedures,views and any other Transact SQL commands compliant
with Microsoft SQL 2000.

Sample Data:
NAMES table content
ID NAME
1 Frank
2 Jo
3 Mary
4 Peter
5 Amy

RELATIONSHIPS table content

NAMEID PARENT_NAMEID
1 0
2 1
3 2
4 1
5 2

Expected Output

LEVEL ID NAME
0 1 Frank
1 2 Jo
2 5 Amy
2 3 Mary
1 4 Peter

Thanks in advanced

•

Join Date: Feb 2008

Posts: 14

Reputation: huangzhi is an unknown quantity at this point

Rep Power: 1

Solved Threads: 4

huangzhi

Offline

Newbie Poster

Re: Depth Travesal

Mar 19th, 2008

Try this code in Query Analyser. It enough for small data. If you want to use for big data, modify the code.

Help with Code Tags

MSSQL Syntax (Toggle Plain Text)

set nocount on
create table #tmpName (ID int, [Name] varchar(255))
create table #tmpRelation (ID int, ID_Parent int)
 
insert #tmpName (ID, [Name]) select 1, 'Frank'
insert #tmpName (ID, [Name]) select 2, 'Jo'
insert #tmpName (ID, [Name]) select 3, 'Mary'
insert #tmpName (ID, [Name]) select 4, 'Peter'
insert #tmpName (ID, [Name]) select 5, 'Amy'
 
insert #tmpRelation (ID, ID_Parent) select 1, 0
insert #tmpRelation (ID, ID_Parent) select 2, 1
insert #tmpRelation (ID, ID_Parent) select 3, 2
insert #tmpRelation (ID, ID_Parent) select 4, 1
insert #tmpRelation (ID, ID_Parent) select 5, 2
 
create table #tmpResult (ID int, ID_Parent int, [Level] int, [Name] varchar(255))
 
insert #tmpResult (ID, ID_Parent, [Level], [Name])
  select N.ID, R.ID_Parent, 0, N.[Name]
    from #tmpName N inner join #tmpRelation R on
	N.ID = R.ID
    where ID_Parent = 0
 
while exists (select 1 from #tmpName N left join #tmpResult R on N.ID = R.ID where R.ID is null)
begin
  insert #tmpResult (ID, ID_Parent, [Level], [Name])
    select N.ID, R.ID_Parent, C.[Level] + 1, N.[Name]
      from #tmpName N inner join #tmpRelation R on
	N.ID = R.ID inner join (
			select	P.ID, P.[Level]
			  from	#tmpResult P left join #tmpResult C on
				P.ID = C.ID_Parent
			  where C.ID is null
				) C on
	R.ID_Parent = C.ID
end
 
select ID, [Level], Name from #tmpResult
 
drop table #tmpResult
drop table #tmpName
drop table #tmpRelation