Creating an associative table using SQL as values change ....

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.On Tue, 13 Sep 2005 08:01:56 -0600, TroyS wrote:

>I have a table in a database however there are no pk-fk relationships in th
e
>schema.
Hi TroyS,
Fix that first, please. Every table should have a primary key. Every
relationship should be enforced by a FK constraint. Omitting that basic
rule of relational databases is asking for garbage in your data.

>So i need to create a view/temp table which relates the Sublevels to the To
p
>Level values.
>For example, i have the following table:
(snip)
>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.
I'm not sure if this is the best way to store your data, though I'm very
sure that it's a whole lot better than your current way!
Order a copy of Joe Celko's Trees and Hierarchies in SQL For Smarties
now, and read it when you have it to find out all you want to know (and
more) about good and bad ways to model this kind of data.
Anyway, for now I'll give you a query that will hopefully convert your
current mess in the somewhat better version you're asking for:
INSERT INTO BetterTable (LV_ID, Level_Reports_to)
SELECT a.LV_ID,
CASE
WHEN a.LV_Level = 1
THEN a.LV_ID
ELSE (SELECT MAX(b.LV_ID)
FROM BadTable AS b
WHERE b.LV_Level = 1
AND b.LV_ID < a.LV_ID)
END
FROM BadTable AS a
(Untested - see www.aspfaq.com/5006 if you prefer a tested version).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
thx for the info
I realize the schema is flawed, however, this isn't an appl i'm writing.
it's a 3rd party,commercial appl and therefore i can't change the
schema.
If the schema had pk-fk relationships, then i wouldn't need some
help...
i will take a look at the query...thx for the help.
*** Sent via Developersdex http://www.examnotes.net ***