Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Test2 ( ID INT, Value INT, other INT);
CREATE TABLE Test1 ( ID INT, TYPE INT, other INT);
begin
INSERT INTO Test2 VALUES (123456, 5, 12);
INSERT INTO Test2 VALUES (123456, 10, 17);

INSERT INTO Test1 VALUES (123456, 00, 2);
INSERT INTO Test1 VALUES (123456, 01, 6);
INSERT INTO Test1 VALUES (123456, 02, 4);

INSERT INTO Test1 VALUES (987654, 00, 7);
INSERT INTO Test1 VALUES (987654, 01, 8);

INSERT INTO Test1 VALUES (456789, 00, 6);
INSERT INTO Test1 VALUES (456789, 01, 16);
end ;
/
1 rows affected

select T1.id, T1.type, T1.other, T2.value
from (
  select id, type, other
  , row_number() over ( partition by id order by type ) rn_
  from test1
) T1 left join (
  select id, value, other
  , row_number() over ( partition by id order by value ) rn_
  from test2
) T2 on T1.id = T2.id and T1.rn_ = T2.rn_
where exists ( select null from test2 where id = T1.id )
order by 1, 2 ;


ID
TYPE
OTHER
VALUE


123456
0
2
5

123456
1
6
10

123456
2
4
null