Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122

    Arrow Unanswered: Complex relationship

    Due to choices beyond my control I am faced with a tough bit of SQL to compose.

    I have four tables. Let us callt hem A, B, C & D.

    Table B has had it's content wholesale copied into table A. Table A is dynamic and changes table B will never change.

    Table B has a non explicite (unenforced) but real one to many relationship to C.

    [B] --< [C]

    This is due to a unique number for each record within each row of B and none, one or many instances of that number within C.

    These number DID NOT copy to A from B as A will be useing an autonumber instead.

    The relationship between A and B is the presence of something the developers chose to call the SB_Key it is is 99.9% unique with odds of arround 1 in 1000 chance of two identical values in two different fields. However to get SB_Key duplication the records have to be made in the same second in the same table on the same day (etc) so for our purposes they are all different as no user I know can type taht fast.

    [A] --- [B] A has a one to one relationship to B

    Now we come to table D. D has had the content of C copied into it. C is static and D is dynamic.

    Again the SB_Key is the one to one link between the tables.

    [C] --- [D] C has a one to one relation ship with D.

    So far all of these relationships are known only to the programmers and are not explicit.

    Further tables B and C are in another database file altogeather!

    [A] --- [B] --< [C] --- [D]

    No comes the bit with which I am haveing some trouble.

    [A] --< [D] A should have a one to many relationship with D. The relationship of A to D must match the relationship from B to C.

    The data is in place and the plan is to "run an update Query" to replace the Foregn Key in table D with the correct autonumber-generated value from table A based on the relationship between B and C.

    I can not whoever seem to comeup with the SQL that will do this.

    If I have to I can create the SQL dynamicly in VBa code and create VBa functions to go get information.

    However there is a lot of data and the more functions the slower the system. It will be run on PCs ranging from Pentium II to 3 Gig Athlon XP and it is vital that the computer not crash or appear to crash and cause the user to press reset (thus corrupting thier data).

    Help.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sounds like you want to do this:

    update d
    set a_id =
    ( select a_id
    from a, b, c
    where a.sb_key = b.sb_key
    and b.b_id = c.b_id
    and c.sb_key = d.sb_key
    );

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •