Friday, February 24, 2012

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.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)?|||Data from table B can be faked and later updated into C.|||what's wrong on this?:

INSERT INTO TableC
SELECT * FROM TableA
UNION ALL
SELECT * FROM TableB where PersID not in (SELECT PersID FROM TableA)

No comments:

Post a Comment