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 |