Forum Stats

  • 3,770,575 Users
  • 2,253,135 Discussions
  • 7,875,498 Comments

Discussions

Pick a join table from condition

Johnny B
Johnny B Member Posts: 608 Bronze Badge

Greetings,

Here is my sample data:

create table listA (
pe     varchar2(4),
rs_id  varchar2(3)
);

insert into listA ( pe, rs_id )
values ( '025', 'T95');
insert into listA ( pe, rs_id )
values ( '026', 'T94');
insert into listA ( pe, rs_id )
values ( '027', 'T93');

create table listB (
rs_id      varchar2(4),
label_name varchar2(10),
pe         varchar2(4),
bso        number,
li         varchar2(5)
);

insert into listB (rs_id, label_name, pe, bso, li )
values ('T951', 'CNN', '025', 30, '001');
insert into listB (rs_id, label_name, pe, bso, li )
values ('T961', 'CNN', '025', 20, '001');
insert into listB (rs_id, label_name, pe, bso, li )
values ('T962', 'CNN', '026', 30, '001');
insert into listB (rs_id, label_name, pe, bso, li )
values ('T963', 'CNN', '026', 20, '001');
insert into listB (rs_id, label_name, pe, bso, li )
values ('T964', 'CNN', '027', 30, '001');

create table sampleData (
myID       number,
label_name varchar2(10),
pe         varchar2(4),
bso        number,
li         varchar2(5),
amt        number
);

insert into sampleData ( myID, label_name, pe, bso, li, amt)
values (125, 'CNN', '025', 30, '001', 165);

insert into sampleData ( myID, label_name, pe, bso, li, amt)
values (125, 'CNN', '026', 20, '001', 235);

insert into sampleData ( myID, label_name, pe, bso, li, amt)
values (135, 'CNN', '025', 30, '001', 65);

insert into sampleData ( myID, label_name, pe, bso, li, amt)
values (135, 'CNN', '026', 20, '001', 235);

I can find the rs_id based on listB table:

select myID, rs_id, s.label_name, s.pe, s.bso, s.li, amt
from sampleData s
join listB b on b.label_name = s.label_name and b.pe = s.pe and b.bso = s.bso and b.li = s.li
order by 1, 4, 5

In the same query, How can I get the rs_id from listA if the myID is less or equal to 130?

Use the listB if the myID is greater than 130 (as I have).

Using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit

Thanks in advance for your help.

V/r Johnny

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,234 Red Diamond
    edited Oct 27, 2021 5:11PM Accepted Answer

    Hi, @Johnny B

    Thanks for posting the CREATE TABLE and INSERT statements. Don't forget to post the exact results you want from that sample data.

    In the same query, How can I get the rs_id from listA if the myID is less or equal to 130?

    Use the listB if the myID is greater than 130 (as I have).

    One approach is to join sampleData to the UNION of listA and listB, like this:

    WITH  listAB  AS
    (
      SELECT   rs_id, label_name, pe, bso, li
      ,	  'LISTB' AS list_table
      FROM    listB
    UNION ALL
      SELECT  rs_id, NULL, pe, NULL, NULL
      ,	  'LISTA' AS list_table
      FROM    listA
    )
    SELECT   s.myID, l.rs_id, s.label_name, s.pe, s.bso, s.li, s.amt
    FROM 	 sampleData s
    JOIN 	 listAB     l    ON  l.pe = s.pe
    	 	   	 AND (  (    s.myID      <= 130
    			   	 AND l.list_table = 'LISTA'
    				 )
    			      OR (   s.myID       > 130
    			   	 AND l.list_table = 'LISTB'
    			   	 AND l.bso 	  = s.bso
    				 AND l.li 	  = s.li
    				 AND l.label_name = s.label_name
    				 )
    			   )
    ORDER BY s.myID, s.pe, s.bso
    ;
    

    I'm just guessing at the join conditions when myID <= 130.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,926 Red Diamond

    ListA & ListB have different set of columns, so what do you expect back when myID is less or equal to 130?

    SY.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,234 Red Diamond
    edited Oct 27, 2021 5:11PM Accepted Answer

    Hi, @Johnny B

    Thanks for posting the CREATE TABLE and INSERT statements. Don't forget to post the exact results you want from that sample data.

    In the same query, How can I get the rs_id from listA if the myID is less or equal to 130?

    Use the listB if the myID is greater than 130 (as I have).

    One approach is to join sampleData to the UNION of listA and listB, like this:

    WITH  listAB  AS
    (
      SELECT   rs_id, label_name, pe, bso, li
      ,	  'LISTB' AS list_table
      FROM    listB
    UNION ALL
      SELECT  rs_id, NULL, pe, NULL, NULL
      ,	  'LISTA' AS list_table
      FROM    listA
    )
    SELECT   s.myID, l.rs_id, s.label_name, s.pe, s.bso, s.li, s.amt
    FROM 	 sampleData s
    JOIN 	 listAB     l    ON  l.pe = s.pe
    	 	   	 AND (  (    s.myID      <= 130
    			   	 AND l.list_table = 'LISTA'
    				 )
    			      OR (   s.myID       > 130
    			   	 AND l.list_table = 'LISTB'
    			   	 AND l.bso 	  = s.bso
    				 AND l.li 	  = s.li
    				 AND l.label_name = s.label_name
    				 )
    			   )
    ORDER BY s.myID, s.pe, s.bso
    ;
    

    I'm just guessing at the join conditions when myID <= 130.

  • Johnny B
    Johnny B Member Posts: 608 Bronze Badge

    You got it Frank, Thanks !