Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Question Unanswered: I need help, please...

    I have 3 tables - 2 source, 1 destination. All tables have 2 IDs. One
    is ID of a person, the other is sort of a 'segment' ID. This segment ID
    defines the department. The destination table is a new table that is to
    hold data from the old ones.
    The problem is copying from the old tables, coz the IDs of persons do
    not match - i.e. the first table contains IDs not present in the second
    table and the second table contains IDs that the first one doesn't. The
    majority exist in both.
    I first tryed a left join only to find the missing IDs in both tables
    as stated previously. Then I tryed to copy the tables separately of
    each other, then I tryed in reverse order, but foud that I was always
    left with missing data. That is, all person IDs were in the destination
    table with their segment IDs but still missing some data in other
    fields.

    Table A (old)
    +----------+---------+------------------------------+
    | PersID | SegID | -some other data not in B- |
    +----------+---------+------------------------------+
    | 1 | 1 | . . . |
    | 2 | 1 | . . . |
    | 3 | 1 | . . . |
    | 4 | 2 | . . . |
    | 5 | 2 | . . . |
    | 6 | 2 | . . . |
    | 7 | 2 | . . . | <-- missing in B
    | 8 | 2 | . . . | <-- missing in B
    | 9 | 2 | . . . |
    | 11 | 3 | . . . |
    | . . . | . . . | . . . |
    +----------+---------+------------------------------+

    Table B (old)
    +----------+---------+------------------------------+
    | PersID | SegID | -some other data not in A- |
    +----------+---------+------------------------------+
    | 1 | 1 | . . . |
    | 2 | 1 | . . . |
    | 3 | 1 | . . . |
    | 4 | 2 | . . . |
    | 5 | 2 | . . . |
    | 6 | 2 | . . . |
    | 9 | 2 | . . . |
    | 10 | 2 | . . . | <-- missing in A
    | 11 | 2 | . . . | <-- missing in A
    | 11 | 3 | . . . |
    | . . . | . . . | . . . |
    +----------+---------+------------------------------+

    Table C (new)
    +----------+---------+-----------------------------+
    | PersID | SegID | -other data from A and B- |
    +----------+---------+-----------------------------+
    | 1 | 1 | . . . |
    | 2 | 1 | . . . |
    | 3 | 1 | . . . |
    | 4 | 2 | . . . |
    | 5 | 2 | . . . |
    | 6 | 2 | . . . |
    | 7 | 2 | . . . | <-- not a problem
    | 8 | 2 | . . . | <-- not a problem
    | 9 | 2 | . . . |
    | 10 | 2 | . . . | <-- not a problem
    | 11 | 2 | . . . | <-- not a problem
    | 11 | 3 | . . . | <-- not a problem
    | . . . | . . . | . . . |
    +----------+---------+-----------------------------+

    Table C has data missing because some data isn't present in A or B. In
    addition C has missing data that tables A or B do have!!! This is the
    problem I'm solving for some time now and am at an end (with nervs,
    ideas, etc.).

    I hope I've explained the problem thoroughly enough and that you can
    help.

    I am very close to dead line so please help...

    P.S.
    DB is Oracle 9.2

    Thank you.

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Do you JOIN Table (A) and Table (B) together? I mean do you have to join them to populate Table C properly (some columns are only in Table A and some only in Table B) or if you insert e.g. record from A you insert all its attributes into C (you don't need any attribute from Table B)?

  3. #3
    Join Date
    Sep 2003
    Posts
    5
    Data from table B can be faked and later updated into C.

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    what's wrong on this?:
    Code:
    INSERT INTO TableC
    SELECT * FROM TableA
    UNION ALL
    SELECT * FROM TableB where PersID not in (SELECT PersID FROM TableA)

Posting Permissions

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