Clash Royale CLAN TAG

retrieve row from multiple row of table in oracle

I want to retrieve data from three table

for example

Table_1 : NAME_A

Table_2 : NAME_B Primary_key PD_ID,EV_N

Table_3 : NAME_C Primary key PD_ID

Required Output

Table_3

2 Answers
2

Try this query with full outer join:

select b.pd_id, b.EV_N, b.EV_DEC,c.FFT_NAME,c.FFT_DESC
from NAME_B b full outer join NAME_C c
on b.pd_id=c.pd_id


The idea is to range records in both tables and then use this range numbers in a full outer join:

with
t1 as (
select 'A' pd_id from dual union all
select 'B' pd_id from dual union all
select 'C' pd_id from dual
),
t2 as (
select 'A' pd_id, 1 EV_N, 'one' EV_DEC from dual union all
select 'A' pd_id, 2 EV_N, 'two' EV_DEC from dual union all
select 'B' pd_id, 1 EV_N, 'one' EV_DEC from dual union all
select 'B' pd_id, 2 EV_N, 'two' EV_DEC from dual union all
select 'B' pd_id, 3 EV_N, 'three' EV_DEC from dual union all
select 'C' pd_id, 1 EV_N, 'one' EV_DEC from dual union all
select 'C' pd_id, 2 EV_N, 'two' EV_DEC from dual
),
t3 as (
select 'A' pd_id, 'XY' FFT_NAME, 'XY_DESC' FFT_DESC from dual union all
select 'B' pd_id, 'ZY' FFT_NAME, 'ZY_DESC' FFT_DESC from dual union all
select 'B' pd_id, 'XY' FFT_NAME, 'XY_DESC' FFT_DESC from dual union all
select 'C' pd_id, 'ZY' FFT_NAME, 'ZY_DESC' FFT_DESC from dual union all
select 'C' pd_id, 'XY' FFT_NAME, 'XY_DESC' FFT_DESC from dual union all
select 'C' pd_id, 'PY' FFT_NAME, 'PY_DESC' FFT_DESC from dual
)
select coalesce(t22.pd_id,t33.pd_id) pd_id,
t22.ev_dec,
t33.FFT_NAME,
t33.FFT_DESC
from (
select pd_id, ev_n, ev_dec, row_number() over (partition by pd_id order by ev_n, ev_dec) rn
from t2
) t22
full join (
select pd_id, FFT_NAME, FFT_DESC, row_number() over (partition by pd_id order by FFT_NAME, FFT_DESC) rn
from t3
) t33
on t22.pd_id = t33.pd_id
and t22.rn = t33.rn


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.