CODEWITHSUNDEEP
sqloracle-database
Alphonsa
Alphonsa

Reputation: 39

Oracle SQL - Listing records from Parent and Child Table in a certain order

I have two tables T1 (Parent_Table) and T2 (Child_Table)

T1:

ID | LANG | ASSURANCE | VAL |
 1 |  L1  |   ASSR1   |  0  |
 1 |  L2  |   ASSR2   |  1  |
 1 |  L3  |   ASSR3   |  5  |

T2:

ID | LANG | File | ASSURANCE | VAL |
 1 |  L1  |  F1  |   ASSR1   |  0  |
 1 |  L1  |  F2  |   ASSR1   |  4  |
 1 |  L2  |  F3  |   ASSR2   |  8  |
 1 |  L2  |  F4  |   ASSR2   |  3  |
 1 |  L3  |  F5  |   ASSR3   |  7  |
 1 |  L3  |  F6  |   ASSR3   |  0  |

Output expected:

ID | File | ASSURANCE | VAL |
 1 |  L1  |   ASSR1   |  0  | - Matched row from T1 for id=1 and lang=l1 
 1 |  F1  |   ASSR1   |  0  | - Matched row from T2 for id=1 and lang=l1 
 1 |  F2  |   ASSR1   |  4  | - Matched row from T2 for id=1 and lang=l1 
 1 |  L2  |   ASSR2   |  1  | - Matched row from T1 for id=1 and lang=l2
 1 |  F3  |   ASSR2   |  8  | - Matched row from T2 for id=1 and lang=l2
 1 |  F4  |   ASSR2   |  3  | - Matched row from T2 for id=1 and lang=l2
 1 |  L3  |   ASSR3   |  5  | - Matched row from T1 for id=1 and lang=l3
 1 |  F5  |   ASSR3   |  7  | - Matched row from T2 for id=1 and lang=l3
 1 |  F6  |   ASSR3   |  0  | - Matched row from T2 for id=1 and lang=l3

Requirement:

I want to traverse through T1 for id = 1 and for every lang, I want to first output that row(from T1) and then traverse in T2 for the id and current lang and the list of matched records in T2 (for id and current lang) then proceed with the next lang in T1 till all the langs are processed in T1.

Is it possible to achieve it using a single Oracle SQL query?

Thank you.

Upvotes: 0

Views: 71

Answers (3)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Glue tables using union all, adding info about source of rows. For first table also add column lang as file_. Finally sort this union.

select id, file_, assurance, val
  from (
    select id, lang, lang file_, assurance, val, 't1' source from t1 where id = 1
    union all
    select id, lang, file_, assurance, val, 't2' source from t2 where id = 1)
  order by lang, source, file_

dbfiddle demo

Upvotes: 1

Ted at ORCL.Pro
Ted at ORCL.Pro

Reputation: 1612

Try this:

with 
    T1 as (
            select 1 as id, 'L1' as LANG, 'ASSR1' as assurance, 0 as VAL from dual union
            select 1, 'L2', 'ASSR2',1 from dual union
            select 1, 'L3','ASSR3',5 from dual),
    T2 as (
            select 1 as id , 'L1' as lang, 'F1' as "FILE", 'ASSR1' as assurance, 0 as val from dual union
            select 1,'L1','F2','ASSR1',4 from dual union
            select 1,'L2','F3','ASSR2',8 from dual union
            select 1,'L2','F4','ASSR2',3 from dual union
            select 1,'L3','F5','ASSR3',7 from dual union
            select 1,'L3','F6','ASSR3',0 from dual),
    T3 as (select t2.id, t2.lang, t2."FILE", t2.assurance, t2.val, row_number() over (partition by t2.id, t2.lang order by t2."FILE") as pos
           from T2    
             inner join t1 on t1.id = t2.id and t1.lang = t2.lang)
select t1.id, t1.lang, t1.lang as "FILE", t1.assurance, t1.val, 0 as pos from T1
union
select t3.id, t3.lang, t3."FILE", t3.assurance, t3.val,  t3.pos
from t3
order by id,lang,pos

Output:

ID  LANG    FILE    ASSURANCE   VAL POS
1   L1  L1  ASSR1   0   0
1   L1  F1  ASSR1   0   1
1   L1  F2  ASSR1   4   2
1   L2  L2  ASSR2   1   0
1   L2  F3  ASSR2   8   1
1   L2  F4  ASSR2   3   2
1   L3  L3  ASSR3   5   0
1   L3  F5  ASSR3   7   1
1   L3  F6  ASSR3   0   2

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

Something like this?

SQL> with t1 (id, lang, assurance, val) as
  2  (select 1, 'L1', 'assr1', 0 from dual union all
  3   select 1, 'L2', 'assr2', 1 from dual union all
  4   select 1, 'L3', 'assr3', 5 from dual
  5  ),
  6    t2 (id, lang, cfile, assurance, val) as
  7  (select 1, 'L1', 'F1', 'assr1', 0 from dual union all
  8   select 1, 'L1', 'F2', 'assr1', 4 from dual union all
  9   select 1, 'L2', 'F3', 'assr2', 8 from dual union all
 10   select 1, 'L2', 'F4', 'assr2', 3 from dual union all
 11   select 1, 'L3', 'F5', 'assr3', 7 from dual union all
 12   select 1, 'L3', 'F6', 'assr3', 0 from dual
 13  ),
 14    inter as
 15  (select t1.id, t1.lang, t1.lang cfile, t1.assurance, t1.val from t1
 16   union all
 17   select t2.id, t2.lang, t2.cfile, t2.assurance, t2.val from t2
 18  )
 19  select id, cfile, assurance, val
 20  from inter
 21  order by id, lang, cfile desc;

        ID CF ASSUR        VAL
---------- -- ----- ----------
         1 L1 assr1          0
         1 F2 assr1          4
         1 F1 assr1          0
         1 L2 assr2          1
         1 F4 assr2          3
         1 F3 assr2          8
         1 L3 assr3          5
         1 F6 assr3          0
         1 F5 assr3          7

9 rows selected.

SQL>

Upvotes: 0

Related Questions