Looking for query using oracle or ways to implement the below requirement

Question

Looking for solution using oracle or ways to implement the below requirement

I have two tables TABLE A and TABLE B Where the primary key in the TABLE A is the foreign key in TABLE B

The goal is to write the query to select the rows from the table A and for each row from TABLE A select the rows from the child table B where the primary key of the TABLE A matches foreign key of TABLE B

TABLE A

col1.PK     col2        col3        col4         col5       col6
AXXXXX      AXXXX       QQQQ        XXXX         XXXXa      xxxxa
BXXXXX      BXXXX       QQQX        XXXX         XXXXB      vvvvb
CXXXXX      CXXXX       QQQC        XXXX         XXXXD      xxxxd

TABLE B

colb.PK     col1.fk     col3        col4         col5       col6
AXXXXX      AXXXX       QQQQ        XXXX         XXXXa      xxxxa
BXXXXX      BXXXX       QQQX        XXXX         XXXXB      vvvvb
CXXXXX      CXXXX       QQQC        XXXX         XXXXD      xxxxd

FINAL OUTPUT

colAB1      colAB2      colAB3      colAB4       colAB5     colAB6
AXXXXX      AXXXX       QQQQ        XXXX         XXXXa      xxxxa  ---- Parent RECORD FROM TABLE A
AXXXXX      AXXXX       QQQQ        XXXX         XXXXa      xxxxa  ----- child record ( matching child records)
BXXXXX      BXXXX       QQQX        XXXX         XXXXB      vvvvb  ----- child record ( matching child records)
CXXXXX      CXXXX       QQQC        XXXX         XXXXD      xxxxd  ----- child record ( matching child records)

colAB1      colAB2      colAB3      colAB4       colAB5     colAB6
BXXXXX      BXXXX       QQQX        XXXX         XXXXB      vvvvb  ---- Parent RECORD FROM TABLE A
AXXXXX      AXXXX       QQQQ        XXXX         XXXXa      xxxxa  ----- child record ( matching child records)
BXXXXX      BXXXX       QQQX        XXXX         XXXXB      vvvvb  ----- child record ( matching child records)
CXXXXX      CXXXX       QQQC        XXXX         XXXXD      xxxxd  ----- child record ( matching child records

colAB1      colAB2      colAB3      colAB4       colAB5     colAB6
CXXXXX      CXXXX       QQQC        XXXX         XXXXD      xxxxd  ---- Parent RECORD FROM TABLE A
AXXXXX      AXXXX       QQQQ        XXXX         XXXXa      xxxxa  ----- child record ( matching child records)
BXXXXX      BXXXX       QQQX        XXXX         XXXXB      vvvvb  ----- child record ( matching child records)
CXXXXX      CXXXX       QQQC        XXXX         XXXXD      xxxxd  ----- child record ( matching child records)

s For each row from table A there will be three matching rows from table B

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-10-28T20:30:22

Something like this:

select *
from ((select a.*, 1 as is_a
       from a
      ) union all
      (select b.*, 0 as is_a
       from b
      )
     ) ab
order by (case when is_a then col1 else col2 end), -- col2 is the foreign key reference
         is_a desc,
         col1

Looking for query using oracle or ways to implement the below requirement

1 Answers