Thanks in Advance
I have 2 tables Table_aa2 and table_aa3
where region and RN rows data are common
I want to join on 2 tables and get the missing RN number row
1) for Example
Example 1
select * from table_aa3 where region = 1 order by region,rn
A BEG END PROF AVERAGE REGION RN
1 0 0.1 159 159 1 1
3 0.2 0.3 179 159 1 3
4 0.1 0.2 250 300 1 4
In tha above query I want to display the RN = 2 from Table_aa2 for Region = 1 by joing above output from table_aa3
A BEG END PROF AVERAGE REGION RN
1 0 0.1 159 159 1 1
*2 0.1 0.2 159 168 1 2*
3 0.2 0.3 179 159 1 3
4 0.1 0.2 250 300 1 4
Example 2
select * from table_aa3 where region in(1,2) order by region,rn
A BEG END PROF AVERAGE REGION RN
1 0 0.1 159 159 1 1
3 0.2 0.3 179 159 1 3
4 0.1 0.2 250 300 1 4
5 0.2 0.3 320 250 2 1
7 0.2 0.3 388 379 2 3
8 0.3 0.4 379 388 2 4
9 0.4 0.5 388 400 2 5
10 1.5 0.6 499 500 2 6
In tha above query I want to display the RN = 2,6 from Table_aa2 for Region = 1,2 (by joing above output from table_aa3)
A BEG END PROF AVERAGE REGION RN
1 0 0.1 159 159 1 1
*2 0.1 0.2 159 168 1 2*
3 0.2 0.3 179 159 1 3
4 0.1 0.2 250 300 1 4
5 0.2 0.3 320 250 2 1
*6 0.3 0.4 250 380 2 2*
7 0.2 0.3 388 379 2 3
8 0.3 0.4 379 388 2 4
9 0.4 0.5 388 400 2 5
10 1.5 0.6 499 500 2 6
Example 3
select * from table_aa3 where region in(1,3) order by region,rn
A BEG END PROF AVERAGE REGION RN
1 0 0.1 159 159 1 1
3 0.2 0.3 179 159 1 3
4 0.1 0.2 250 300 1 4
11 0.5 0.6 420 448 3 1
12 0.6 0.7 520 530 3 2
13 0.7 0.8 540 550 3 3
14 0.9 1 560 570 3 4
In tha above query I want to display the RN = 2 from Table_aa2 for Region = 19by joing above output from table_aa3)
A BEG END PROF AVERAGE REGION RN
1 0 0.1 159 159 1 1
*2 0.1 0.2 159 168 1 2*
3 0.2 0.3 179 159 1 3
4 0.1 0.2 250 300 1 4
11 0.5 0.6 420 448 3 1
12 0.6 0.7 520 530 3 2
13 0.7 0.8 540 550 3 3
14 0.9 1 560 570 3 4
create table TABLE_AA2
(
A NUMBER,
BEG NUMBER,
END NUMBER,
PROF NUMBER,
AVERAGE NUMBER,
REGION NUMBER,
RN NUMBER
)
;
prompt Loading TABLE_AA2...
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (1, 0, .1, 159, 159, 1, 1);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (2, .1, .2, 159, 168, 1, 2);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (3, .2, .3, 179, 159, 1, 3);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (4, .1, .2, 250, 300, 1, 4);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (5, .2, .3, 320, 250, 2, 1);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (6, .3, .4, 250, 380, 2, 2);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (7, .2, .3, 388, 379, 2, 3);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (8, .3, .4, 379, 388, 2, 4);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (9, .4, .5, 388, 400, 2, 5);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (10, 1.5, .6, 499, 500, 2, 6);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (11, .5, .6, 420, 448, 3, 1);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (12, .6, .7, 520, 530, 3, 2);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (13, .7, .8, 540, 550, 3, 3);
insert into TABLE_AA2 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (14, .9, 1, 560, 570, 3, 4);
commit;
create table TABLE_AA3
(
A NUMBER,
BEG NUMBER,
END NUMBER,
PROF NUMBER,
AVERAGE NUMBER,
REGION NUMBER,
RN NUMBER
)
;
prompt Loading TABLE_AA3...
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (1, 0, .1, 159, 159, 1, 1);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (3, .2, .3, 179, 159, 1, 3);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (4, .1, .2, 250, 300, 1, 4);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (5, .2, .3, 320, 250, 2, 1);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (7, .2, .3, 388, 379, 2, 3);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (8, .3, .4, 379, 388, 2, 4);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (9, .4, .5, 388, 400, 2, 5);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (10, 1.5, .6, 499, 500, 2, 6);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (11, .5, .6, 420, 448, 3, 1);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (12, .6, .7, 520, 530, 3, 2);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (13, .7, .8, 540, 550, 3, 3);
insert into TABLE_AA3 (A, BEG, END, PROF, AVERAGE, REGION, RN)
values (14, .9, 1, 560, 570, 3, 4);
commit;
Edited by: user1849 on Sep 8, 2009 3:05 PM