I have a very strange database here to work with, but I came up with a simplified data model that hopefully will demonstrate the problem I am having.
This is going to be a long post. thank you in advance for having the patience to follow thru.
-----
I have two sets of tables that hold multiple "versions" of a given data element. Each time the user makes a change to the data, we add a new record with the same ID but with an incrementing version number (it makes comparisons with historical data much easier than dealing with log tables).
So for each of these sets of tables I have a table with the cumulative records and all the change data, and a "base" table that has a column for the ID and a column for the current version number (and some other common data).
-----
Here is my example schema so far:
create table temp_test_1 (tab1_id number, tab1_v number, tab1_name varchar2(200));
create table temp_base_1 (tab1_id number, tab1_max number);
create table temp_test_2 (tab2_id number, tab2_v number, tab2_name varchar2(200));
create table temp_base_2 (tab2_id number, tab2_max number);
(constraints omitted for simplicity, but in production they would be on (ID, V) for test tables and on ID only for the base table.)
So to get the "current" data for test_1 I would do
SELECT T1.tab1_id, T1.tab1_v, T1.tab1_name
FROM temp_test_1 T1
JOIN temp_base_1 TB1 ON T1.tab1_id =TB1.tab1_id
AND T1.tab1_v =TB1.tab1_max
This works great for temp_test_1 and 2.
-----
Now here comes the problem. I need to join the tables together.
create table temp_join (tab1_id number, tab1_v number, tab2_id number, tab2_v number);
Now my join query is as follows:
SELECT T1.tab1_id, T1.tab1_v, T1.tab1_name, T2.tab2_id, T2.tab2_v, T2.tab2_name
FROM temp_test_1 T1
JOIN temp_base_1 TB1 ON T1.tab1_id =TB1.tab1_id
AND T1.tab1_v =TB1.tab1_max
JOIN temp_join JJ ON T1.tab1_id =JJ.tab1_id
AND T1.tab1_v =JJ.tab1_v
JOIN temp_test_2 T2 ON JJ.tab2_id =T2.tab2_id
AND JJ.tab2_v =T2.tab2_v
JOIN temp_base_2 TB2 ON T2.tab2_id =TB2.tab2_id
AND T2.tab2_v =TB2.tab2_max
This works great for showing all joined records. We have similar queries in production now that are doing exactly what we want.
-----
Now the problem is I need a LEFT JOIN situation. I need all the records from TAB_1, but they may not have joined entries in TAB2.
-----
Here is a script to create a sample data model with sample data
drop table temp_test_1;
drop table temp_base_1;
drop table temp_test_2;
drop table temp_base_2;
drop table temp_join;
create table temp_test_1 (tab1_id number, tab1_v number, tab1_name varchar2(200));
insert into temp_test_1 values (1, 1, 'tab 1 val 1,1');
insert into temp_test_1 values (1, 2, 'tab 1 val 1,2');
insert into temp_test_1 values (1, 3, 'tab 1 val 1,3');
insert into temp_test_1 values (2, 1, 'tab 1 val 2,1');
insert into temp_test_1 values (3, 1, 'tab 1 val 3,1');
insert into temp_test_1 values (4, 1, 'tab 1 val 4,1');
insert into temp_test_1 values (4, 2, 'tab 1 val 4,2');
create table temp_base_1 (tab1_id number, tab1_max number);
insert into temp_base_1 values (1, 3);
insert into temp_base_1 values (2, 1);
insert into temp_base_1 values (3, 1);
insert into temp_base_1 values (4, 2);
create table temp_test_2 (tab2_id number, tab2_v number, tab2_name varchar2(200));
insert into temp_test_2 values (1, 1, 'tab 2 val 1,1');
insert into temp_test_2 values (1, 2, 'tab 2 val 1,2');
insert into temp_test_2 values (1, 3, 'tab 2 val 1,3');
insert into temp_test_2 values (2, 1, 'tab 2 val 2,1');
insert into temp_test_2 values (3, 1, 'tab 2 val 3,1');
insert into temp_test_2 values (4, 1, 'tab 2 val 4,1');
insert into temp_test_2 values (4, 2, 'tab 2 val 4,2');
create table temp_base_2 (tab2_id number, tab2_max number);
insert into temp_base_2 values (1, 3);
insert into temp_base_2 values (2, 1);
insert into temp_base_2 values (3, 1);
insert into temp_base_2 values (4, 2);
create table temp_join (tab1_id number, tab1_v number, tab2_id number, tab2_v number);
insert into temp_join values (1, 1, 1, 1);
insert into temp_join values (1, 2, 1, 1);
insert into temp_join values (1, 3, 1, 1);
insert into temp_join values (4, 2, 4, 1);
insert into temp_join values (4, 2, 4, 2);
commit;
-----
Our best pass at a query so far is:
SELECT T1.tab1_id "ID 1", T1.tab1_v "V 1", T1.tab1_name "Name 1",
T2.tab2_id "ID 2", T2.tab2_v "V 2", T2.tab2_name "Name 2"
FROM temp_test_1 T1
JOIN temp_base_1 TB1 ON T1.tab1_id =TB1.tab1_id
AND T1.tab1_v =TB1.tab1_max
LEFT JOIN temp_join JJ ON T1.tab1_id =JJ.tab1_id
AND T1.tab1_v =JJ.tab1_v
LEFT JOIN temp_test_2 T2 ON JJ.tab2_id =T2.tab2_id
AND JJ.tab2_v =T2.tab2_v
LEFT JOIN temp_base_2 TB2 ON T2.tab2_id =TB2.tab2_id
AND T2.tab2_v =TB2.tab2_max
Based on the data above, I would expect:
ID 1 V 1 Name 1 ID 2 V 2 Name 2
4 2 tab 1 val 4,2 4 2 tab 2 val 4,2
2 1 tab 1 val 2,1
3 1 tab 1 val 3,1
1 3 tab 1 val 1,3
But the query produces the result:
ID 1 V 1 Name 1 ID 2 V 2 Name 2
4 2 tab 1 val 4,2 4 2 tab 2 val 4,2
2 1 tab 1 val 2,1
3 1 tab 1 val 3,1
4 2 tab 1 val 4,2 4 1 tab 2 val 4,1
1 3 tab 1 val 1,3 1 1 tab 2 val 1,1
Note record 5. That record should not be there, but so far our team has not found a way to remove it.
Also record 6 should be blank on the right side since the current value for ID=1 in Tab 1 do not line up with anything current in Tab2 (ID=1 in tab2 has a current value of 3).
-----
Any thoughts that do not involve redesigning our whole data model would be most appreciated. (I would even entertain data model changes, but it would be a tough sell to the rest of the team)
Edited by: wubdeveloper on Apr 28, 2010 2:47 PM
Fixed formatting