Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table A (PID INT, PromotionName VARCHAR(255));
create table B (promotion_name VARCHAR(255), itm_name VARCHAR(255), quantity INT);
INSERT INTO A VALUES(1, 'OUT_EC_D10_V500K_FamilyCare_PROCO');
1 rows affected

INSERT INTO A VALUES(2, 'OUT_EC_D5_V50K_Lunchbox_PROCO');
1 rows affected

INSERT INTO A VALUES(3, 'OUT_EC_D5_V50K_PROCO');
1 rows affected

INSERT INTO B VALUES('Lunchbox', 'Item name 1', 1);
1 rows affected

INSERT INTO B VALUES('FamilyCare', 'Item name 2', 1);
1 rows affected

INSERT INTO B VALUES('FamilyCare', 'Item name 3', 1);
1 rows affected

INSERT INTO B VALUES('BUY1FREE6', 'Item name 4', 1);
1 rows affected

INSERT INTO B VALUES('HiSummer', 'Item name 5', 1);
1 rows affected

INSERT INTO B VALUES('FamilyCare', 'Item name 6', 1);
1 rows affected

SELECT * FROM A where pid = '1'
;


PID
PROMOTIONNAME


1
OUT_EC_D10_V500K_FamilyCare_PROCO


SELECT * FROM A inner join B on a.promotionName LIKE '%'||b.promotion_name||'%'


PID
PROMOTIONNAME
PROMOTION_NAME
ITM_NAME
QUANTITY


1
OUT_EC_D10_V500K_FamilyCare_PROCO
FamilyCare
Item name 2
1

1
OUT_EC_D10_V500K_FamilyCare_PROCO
FamilyCare
Item name 3
1

1
OUT_EC_D10_V500K_FamilyCare_PROCO
FamilyCare
Item name 6
1

2
OUT_EC_D5_V50K_Lunchbox_PROCO
Lunchbox
Item name 1
1


SELECT PID,PromotionName, '' as Itm_name, NULL as quantity 
FROM A 
WHERE pid = '1'

UNION

SELECT PID, PROMOTION_NAME, Itm_name, quantity 
FROM 
(SELECT * FROM A inner join B on a.promotionName LIKE '%'||b.promotion_name||'%')
WHERE pid='1'


PID
PROMOTIONNAME
ITM_NAME
QUANTITY


1
FamilyCare
Item name 2
1

1
FamilyCare
Item name 3
1

1
FamilyCare
Item name 6
1

1
OUT_EC_D10_V500K_FamilyCare_PROCO
null
null

PID	PROMOTIONNAME	PROMOTION_NAME	ITM_NAME	QUANTITY
1	OUT_EC_D10_V500K_FamilyCare_PROCO	FamilyCare	Item name 2	1
1	OUT_EC_D10_V500K_FamilyCare_PROCO	FamilyCare	Item name 3	1
1	OUT_EC_D10_V500K_FamilyCare_PROCO	FamilyCare	Item name 6	1
2	OUT_EC_D5_V50K_Lunchbox_PROCO	Lunchbox	Item name 1	1