SQL query to select all items from a table based on WHERE condition in

Question

Basicaly, I want to select all items in tbl1 WHERE RANGE_DEP in tbl2 match RANGE in tbl1

tbl1

ID	COD	PROCEDURE	RANGE
1	S1	Procedure 1	MM,ME,MG,TF,CH
2	S2	Procedure 2	ME,MG,CH
3	S3	Procedure 3	MM,MG,TF

tbl2

ID	DEPARTMENT	RANGE_DEP
1	D1	MM,ME,CH
2	D2	TF,CH

I tried with

Declare @Range nvarchar(100) = SELECT RANGE FROM tbl1

SELECT * FROM tbl2 WHERE (SELECT RANGE_DEP FROM tbl1 WHERE DEPARTMENT = D1) IN (SELECT * FROM STRING_SPLIT(@Range, ','))

but I am pretty sure is not the corect way. Can you help, please?

Chetan Sanghani · Answer

Here is a step-by-step approach to solving this: Split the RANGE and RANGE_DEP columns into individual rows. Perform a join based on the split values. Aggregate the results back to match the original format. Here's a SQL query that accomplishes this: WITH SplitTbl1 AS ( SELECT t1.ID, t1.COD, t1.PROCEDURE, TRIM(value) AS RangeValue FROM tbl1 t1 CROSS APPLY STRING_SPLIT(t1.RANGE, ',') ), SplitTbl2 AS ( SELECT t2.ID, t2.DEPARTMENT, TRIM(value) AS RangeDepValue FROM tbl2 t2 CROSS APPLY STRING_SPLIT(t2.RANGE_DEP, ',') ) SELECT DISTINCT t1.ID, t1.COD, t1.PROCEDURE, t1.RANGE FROM tbl1 t1 INNER JOIN SplitTbl1 st1 ON t1.ID = st1.ID INNER JOIN SplitTbl2 st2 ON st1.RangeValue = st2.RangeDepValue ORDER BY t1.ID; Explanation: Splitting tbl1 : We use STRING_SPLIT to split the RANGE column in tbl1 and create a common table expression (CTE) SplitTbl1 with each split value as a new row. Splitting tbl2 : Similarly, we use STRING_SPLIT to split the RANGE_DEP column in tbl2 and create another CTE SplitTbl2 with each split value as a new row. Joining and Selecting: We join tbl1 , SplitTbl1 , and SplitTbl2 based on the split values. This ensures that we only select rows where the split values match. DISTINCT is used to ensure that we do not get duplicate rows in the final result. Notes: TRIM(value) is used to remove any leading or trailing spaces around the split values. The ORDER BY clause ensures that the result is sorted by ID . This query will give you the rows from tbl1 where there is at least one matching value in the RANGE and RANGE_DEP columns.