creating a pk-fk table on the fly

[TroyS]

I have a table in a database however there are no pk-fk relationships in the
schema.
So i need to create a view/temp table which relates the Sublevels to the Top
Level values.

For example, i have the following table:

Name LV_Level LV_ID
Admin 1 1
HR 1 8
Ops 1 11
Issuer 2 12
Acquirer 2 13
.. . .
.. . .
Shared Serv 1 19
Finance 2 20
Facilities 2 21
Legal 2 22

The level 2s and greater indicate sublevels to the level 1.

What i need to be able to do is create a hierarchy so that Issuer (2) and
Acquirer (2) belong to Ops (Level 1, ID 11)
and that Finance (2), Facilities (2) and Legal (2) belong to Shared Serv
(Level 1, ID 19).

The LV_ID gets renumbered as new values in the Application are added to the
database. There is another column not shown that acts a pk/uid, but there is
no relationship in this table other than a sequential renumbering of LV_ID.

So if i add anouther value under Ops, Shared Serv may get renumbered to 20
and all the items below it are renumbered as well.

I need to be able account for growth in the tables are new values are added.
I was thinking of something along the results of:

LV_ID Level_Reports_to
1 1
8 8
11 11
12 11
13 11
19 19
20 19
21 19
22 19

I've tried various ways and am not accomplishing the results above.
Any hints on syntax in SQL would be appreciated.

 

[Jan De Messemaeker]

Hi,

Is this Microsoft Project?
(That is the topic of this Newsgroup)

 

[TroyS]

yes. it's an add on to project server call EPK

Hi,

Is this Microsoft Project?
(That is the topic of this Newsgroup)

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/
+32-495-300 620