Hello Experts;
I have the following sample code below
with t(id, type_info, primary_place) as
(
select '10000A', 'AZM', '1st spiderman av' from dual
union all
select '10000B', 'HJJ', '1st spiderman av' from dual
union all
select '10000C', 'L11', '5th zone road' from dual
union all
select '10000D', 'MXX', '11th wolverine place' from dual
union all
select '20000A', 'A11', '2nd hulkman av' from dual
union all
select '20000B', 'MMM', '10th antman crescent' from dual
union all
select '30000A', 'AFP', '3rd wonderwoman av' from dual
)
,t_sub(id, type_info, no_info, secondary_place) as
(
select '10000', 'A', 1, '111 penguin district' from dual
union all
select '10000', 'A', 2, '123 dog St' from dual
union all
select '10000', 'A', 3, '144 cat block' from dual
union all
select '10000', 'A', 4, '555 fish drive' from dual
union all
select '10000', 'B', 1, '477 shark road' from dual
union all
select '10000', 'B', 2, '134 whale ave' from dual
union all
select '10000', 'A', 5, '777 zone place' from dual
union all
select '10000', 'C', 1, '999 monkey city' from dual
union all
select '20000', 'A', 1, '4th zone ave' from dual
union all
select '20000', 'A', 2, '3rd spider place' from dual
union all
select '30000', 'A', 1, '6th elephant ave' from dual
union all
select '30000', 'A', 2, '7th bat road' from dual
)
select * from t_sub
desired outcome
id type_info place_info
10000 A '1st spiderman av'
10000 A1 '111 penguin district'
10000 A2 '123 dog St'
10000 A3 '144 cat block'
10000 A4 '555 fish drive'
10000 A5 '777 zone place'
10000 B '1st spiderman av'
10000 B1 '477 shark road'
10000 B2 '134 whale ave'
10000 C '5th zone road'
10000 C1 '999 monkey city'
10000 D '11th wolverine place'
20000 A '2nd hulkman av'
20000 A1 '4th zone ave'
20000 A2 '3rd spider place'
30000 A '3rd wonderwoman av'
30000 A1 '6th elephant ave'
30000 A2 '7th bat road'
The desired outcome is list the associated parent address from t table and then list all the child addresss from t_sub . the join is based on t(id) suffix and the t_sub(id)
All help is appreciated thanks