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

    Red face Unanswered: Please help! Complicated problem...

    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...

    Thank you.

  2. #2
    Join Date
    May 2005
    Location
    Chesterland, OH
    Posts
    9

    Could you create a view to help?

    What about a view of the 2 tables that contains all of your combinations?

    For example

    Create or replace view stuff_for_tablec as (
    (select * from Tablea a, tableb b
    where a.persid = b.persid
    and a.segid = b.segid)
    union
    (select * from tablea a1 where a1.persid not in(select b1.persid
    from tableb b1 where a1.persid = b1.persid and a1.segid = b1.segid)
    union
    select * from tableb b2 where b2.persid not in(select a2.persid
    from tablea a2 there a2.persid = b2.persid))

    You might be able to query the view and see what is going on. You can insert into the new table via the view.

Posting Permissions

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