Here are some tables:
Create Table FN (
FNunid varchar(32) not null,
...,
PRIMARY KEY (FNunid)
);
Create Table Dependent (
DEPunid varchar(32) not null,
FNunid varchar(32) not null,
...,
PRIMARY KEY (DEPunid),
CONSTRAINT `0_69` FOREIGN KEY (FNunid) REFERENCES FN(FNunid)
);
Create Table Action(
ACTunid varchar(32) not null,
FNunid varchar(32) not null,
...
PRIMARY KEY (ACTunid),
CONSTRAINT `0_67` FOREIGN KEY (FNunid) REFERENCES FN (FNunid)
);
The tables were designed to hold information being transferred from a lot
of Lotus Notes databases (that's why the UNID primary keys are 32
characters). Obviously, the assumption was that Dependent and Action
records are children of FN records. This worked fine for a million plus
records -- and then... Some bright people in another office constructed
their Lotus Notes databases so that some Dependent records are children of
other Dependent records, which, through a chain, ultimately connect to an
FN record. Likewise Action records can be children of Dependent or
Dependent-to-a-Dependent records. In these cases, the FNunid field
actually contains the DEPunid of the next higher Dependent record until
you finally get to a record with FNunid = to an FN.FNunid. Do I need to
say kaboom?
I think the way out of this is to create some sort of lookup table that
relates the various levels of dependent records to the root FN record. I
can't seem to get my head around the design of such a table, however. By
definition (and the structure of Lotus Notes databases), every dependent
and action record is, in fact, a child to another dependent or root FN
record. Not every FN record, however, has children of either Dependent or
Action type. In addition, no FN record is a child of any other record. I
thought of something like:
Create Table XRef(
ThisRecordID varchar(32) not null,
ParentRecordID varchar(32) default null
)
But I can't figure out how to write a SQL query that will trace an Action
or Dependent record, at an unknown level in the hierarchy, back to the
root FN. Is there a better way to do this or can you help me with the SQL
statement?
Thanks
Randy
[EMAIL PROTECTED]
Confidentiality Note: This message and any accompanying attachments
contain information from the law firm Fragomen, Del Rey, Bernsen & Loewy,
P.C. which is confidential or privileged. The information is intended to
be for the use of the individual or entity named above. If you are not the
intended recipient, be aware that any disclosure, copying, distribution or
use of the contents of this information is prohibited. If you have
received this e-mail in error, please notify our offices immediately, by
telephone at 212-688-8555, or by e-mail at [EMAIL PROTECTED]
