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

Here is a step-by-step approach to solving this:

1. Split the RANGE and RANGE_DEP columns into individual rows.
2. Perform a join based on the split values.
3. 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:

1. 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.

2. 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.

3. 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.