How add more rows when find string in column Oracle-CodePudding

Would it be possible to add more rows base on Keyword string in SQL ?

table A

PID    PromotionName                                
 1      OUT_EC_D10_V500K_FamilyCare_PROCO
 2      OUT_EC_D5_V50K_Lunchbox_PROCO
 3      OUT_EC_D5_V50K_PROCO

table B

promotion_code     itm_name         quantity

Lunchbox           Item name 1         1
FamilyCare         Item name 2         1
FamilyCare         Item name 3         1
BUY1FREE6          Item name 4         1
HiSummer           Item name 5         1
FamilyCare         Item name 6         1

Example:

SELECT * FROM A where pid = '1';

Output of the SQL should be -

PID   PromotionName                          Itm_name          quantity
 1     OUT_EC_D10_V500K_FamilyCare_PROCO
 2     FamilyCare                           Item name 2         1
 3     FamilyCare                           Item name 3         1
 4     FamilyCare                           Item name 6         1

How to find string with keyword 'FamilyCare' in PromotionName of table A base on promotion_code of table B? If it exist it will add more rows in output

Any help with the SQL?

CodePudding user response：

Here is how you can achieve this:

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'

You have to update your pid in both the places (before and after UNION).

Notice that tables were joined using LIKE operator with % before and after the word. Hence this joins if a part of a string is present in another column.

db<>fiddle link here

CodePudding user response：

An option would be starting to construct a subquery factoring along with joining tables through a.promotionName LIKE '%'||b.promotion_code||'%' condition while filtering by b.promotion_code = 'FamilyCare', then add another query to combine the result sets by UNION ALL, and then enumerate with an id column by ROW_NUMBER() analytic function such as

WITH ab AS
(
 SELECT a.*, b.* 
   FROM a 
   JOIN b 
     ON a.promotionName LIKE '%'||b.promotion_code||'%'
  WHERE b.promotion_code = 'FamilyCare'   
), ab2 AS
(
 SELECT promotion_code, itm_name, quantity
   FROM ab
 UNION ALL
 SELECT DISTINCT promotionName, NULL, NULL
   FROM ab
)
SELECT ROW_NUMBER() OVER (ORDER BY itm_name NULLS FIRST) AS pid,
       a.*
  FROM ab2 a

if there's mismatch for the topmost query, then no row will be returned. eg. that query will check for the existence for the literal you provide

Demo