Query on a Query !

[Corkyballs]

I have an SQL table which holds details of several company’s staffing hierarchies.

Each row in the table called Hierachy has a CodeID which is the auto-numbering key, a Level ID (1 being the top level), a description and a Parent Code ID.

The parent code ID refers back to a Level ID unless it has a top level position in which case the Parent Code ID is equal to 0.

There may be up to 5 levels below the top level position. eg a top level is a company, the second level could be regions, the third level branches, the fourth level departments and the fifth level staff.

There are more than 1 top level companies.

Given a Hierarchy ID how do I construct a query to return the top level position to which it ultimately refers.

Please help as my brain has melted 8-p

 

[redlam]

Something like this might work. In this example, I have 4 levels - 1 = company, 2 = region, 3 = branch and 4 = staff. Entity is just the name of the company, region, branch or staff.

select
cmp.entity as company,
reg.entity as region,
brn.entity as branch,
sta.entity as staff
from
hierarchy cmp left join
hierarchy reg on
cmp.codeid = reg.parentid and
reg.levelid = 2 left join
hierarchy brn on
reg.codeid = brn.parentid and
brn.levelid = 3 left join
hierarchy sta on
brn.codeid = sta.parentid and
sta.levelid = 4
where
cmp.levelid = 1

 

[dvrobin]

The problem here is that you don't know how many levels there are between the Code you're looking for and the top level item. I don't think there is a way to create a single query that will give you the answer you need. However, I was able to come up with a solution using a view and a stored procedure. You might need to brush up procs and cursors if you're not familiar with them.

Here's the table code I created:

CREATE TABLE [dbo].[Hierarchy] (
[CodeID] [int] IDENTITY (1, 1) NOT NULL ,
[LevelID] [int] NULL ,
[Desc] [varchar] (50) NULL ,
[ParentCodeID] [int] NULL
) ON [PRIMARY]

Here's the view code:

CREATE VIEW dbo.VIEW1
AS
SELECT dbo.Hierarchy.CodeID, dbo.Hierarchy.LevelID,
dbo.Hierarchy.[Desc], dbo.Hierarchy.ParentCodeID
FROM dbo.Hierarchy LEFT OUTER JOIN
dbo.Hierarchy Hierarchy1 ON
dbo.Hierarchy.ParentCodeID = Hierarchy1.CodeID


And here's the Stored Procedure code:

CREATE PROCEDURE proc1
(@intCodeID int, @intTopCodeID int OUT)
AS

declare @intTopLevel int
declare @intParentCodeID int
select @intTopLevel = @intCodeID
/*find the Level for the current item*/
declare tempcurs1 cursor for Select LevelID, ParentCodeID from View1 where CodeID=@intCodeID
open tempcurs1
fetch tempcurs1 into @intTopLevel, @intParentCodeID
close tempcurs1
deallocate tempcurs1


while @intTopLevel > 1
begin
declare tempcurs cursor for Select LevelID, ParentCodeID from View1 where CodeID=@intCodeID
open tempcurs
fetch tempcurs into @intTopLevel, @intParentCodeID
if @intParentCodeID >0
select @intCodeID = @intParentCodeID
close tempcurs
deallocate tempcurs
end
select @intTopCodeID = @intCodeID


To call this proc, use the following code:

declare @VarOut int

exec proc1 4,@VarOut OUT

print @VarOut

I populated the table with the following sample data:

CodeID LevelID Desc ParentCodeID
1 1 Level 1a 0
2 2 2a 1
3 3 3a 2
4 4 4a 3
5 5 5 4
6 1 Level 1b 0
7 2 2b 6
8 3 3b 7

Hope this helps!

Dave Robinder, MCSD

 

[Corkyballs]

Dave, I tried your solution on my return to work today and it worked perfectly 1st time.

Thanks for your help.

Have a happy and prosperous New Year.

Regards

Corkyballs