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

 

[braindead2]

I am a bit confused by this table design

if I read correctly it looks something like this

codeid levelid description parentcodeid
1 1 company1 0
2 2 region1 1
3 2 region2 1
4 3 branches1 2
…
where I am confused is you say
"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."
If this is the way you do it then I don’t think it is possible to get who reports to who.
The reason I say this is if I have 2 regions they both would contain a levelid of 2 so any branches below them I can not tell which region they report to
Now if you told me that the parent id refers back to their codeid which is an indexed field then this is easily solved using a recursive function or even subqueries. Am I confused?