Returning rows that contain text from another table

Huy Tran 0
Viewed 27

I have two tables Main and Lookup

Main has a column called [Promotion Codes List] that is a string of different promo codes comma separated.

Lookup has a column of all the individual promotion codes called [Promotion Codes].

I am trying to create a view of the Main table that will filter Main table if if Main.[Promotion Codes List] contains a code that is in the Lookup.[Promotion Codes] table.

Main Table sample data

|id|Promotion Codes List|
|--| ------------------ |
|1 | BR-Coll-210731-10903, BR-NFRD-210731-10124 |
|2 | BR-Coll-210731-10903, BR-Deal-210731-11265 |
|3 | BR-Coll-210731-11037, BR-Deal-210731-11275 |
|4 | BR-Coll-210731-11037 |

Lookup Table sample data

|id|Promotion Codes|
|--| ------------- |
|1 | BR-NFRI-251231-04855 |
|2 | BR-Deal-210731-11265 |
|3 | BR-Coll-210731-11402 |
|4 | BR-Deal-210731-11275 |
|5 | BR-Coll-210731-11037 |

Desired output Main Table

|id|Promotion Codes List|
|--| ------------------ |
|2 | BR-Coll-210731-10903, BR-Deal-210731-11265 |
|3 | BR-Coll-210731-11037, BR-Deal-210731-11275 |
|4 | BR-Coll-210731-11037 |

I started off playing around with

SELECT        Promotion Codes List, CASE WHEN EXISTS (SELECT *
FROM            dbo.[Lookup] B
WHERE        B.[Promotion Codes] = A.Promotion Codes List) THEN 'common' ELSE 'not common' END AS valid FROM dbo.Main AS A

and it works if there is are exact matches in row, but as you can see in the main table there can be multiple br codes in one record so I tried to add a like statement somewhere but couldn't figure it out.

Thanks !

exists sql-like sql-server sql
1 Answers

Try below

 SELECT        Promotion Codes List, CASE WHEN EXISTS (SELECT 1
    FROM            dbo.[Lookup] B
    WHERE    A.[Promotion Codes List] like '%'+B.[Promotion Codes]+'%') THEN 'common' ELSE 'not common' END AS valid FROM dbo.Main AS A
jitender0
Related