Выберите записи из таблицы, результаты которых соответствуют всем критериям в подзапросе

Обычно условие любого критерия применяется с помощью WHERE EXISTS(SELECT * FROM Criteria WHERE criteria-passes), а условие всех критериев можно применить с помощью WHERE NOT EXISTS(SELECT * FROM Criteria WHERE NOT criteria-passes).

Я считаю, что в этом случае вам нужно последнее:

select *
from QryTable QT
where not exists (
    select *
    from CriterionTbl CT
    where QT.CSV_Vals not like '%' + CT.CriterionVals + '%'
    where ',' + QT.CSV_Vals + ',' not like '%,' + CT.CriterionVals + ',%'
)

Я добавил запятые в приведенный выше тест, чтобы избежать ложных совпадений между такими строками, как «val_2» и «val_20».

Также можно использовать оператор ALL() с небольшими уговорами и выражением CASE, которое отображает успех или неудачу в 1 или 0.

select *
from QryTable QT
where 1 = all (
    select case when ',' + QT.CSV_Vals + ',' like '%,' + CT.CriterionVals + ',%' then 1 else 0 end
    from CriterionTbl CT
)

Стоит отметить, что если CriterionTbl пусто, условие всегда будет истинным.

Результаты одинаковы для обоих вышеперечисленных вариантов:

См. эту db<>fiddle для демонстрации. (Спасибо @BartMcEndree за первоначальную настройку скрипки.)

это не идеально, просто другой подход. но один из вариантов — разделить данные, сравнить с ключевыми словами и выбрать записи, в которых счетчик соответствует всем условиям.

declare @QryTable Table (ID [int], Description [nvarchar](255), CSV_Vals [nvarchar](255))
insert into @QryTable 
VALUES
(1, 'Description of record #1', 'val_1,val_2,val_3,val_4,val_5,val_6'),
(2, 'Description of record #2', 'val_1,val_3,val_6,val_9,val_10,val_11'),
(3, 'Description of record #3', 'val_2,val_3,val_4,val_15,val_20,val_21')

declare @CriterionTbl Table  (ID [int] identity (1,1), CriterionVals [nvarchar](50))
insert into @CriterionTbl VALUES ('val_3'), ('val_4');

declare @maxCount int = 0;
select @maxCount = Count(*) from @CriterionTbl

Select * from  @QryTable
where id in (
    select ID FROM
    (
        select rn = ROW_NUMBER() over ( partition by id order by id), 
            *
        from 
            (select id, CSV_Vals, value from @QryTable cross apply String_split(CSV_Vals, ',')) t
        inner join 
            (select CriterionVals FROM @CriterionTbl ) ct on t.value = ct.CriterionVals 
    ) x 
     group by id having count(id) = @maxCount
)

ОБНОВЛЕНИЕ: (спасибо, T.N) для упрощенной версии я добавлю к ответу - чтобы ее можно было найти
)

-- count(distinct ...) is used to allow handle possible duplicates in either table
declare @maxCount int = (select Count(distinct CriterionVals) from CriterionTbl)

Select qt.*
from QryTable qt
where @maxCount = (
    select count(distinct ct.CriterionVals)
    from String_split(qt.CSV_Vals, ',') t
    inner join CriterionTbl ct
        on t.value = ct.CriterionVals 
)

результат будет

См. пример. Там мы можем использовать любой подходящий подзапрос вместо CriterionTbl в качестве inner join (select x as CriterionVals from anyTable ) as c.

select *
from(
  select q.* 
    ,row_number()over(partition by q.id order by q.id) rn
    ,count(*)over(partition by q.id) qty
  from QryTable q
  inner join CriterionTbl c 
    on concat(',',CSV_vals,',') like concat('%,',c.CriterionVals,',%')
)t
where qty=(select count(*) from CriterionTbl) and rn=1

Там путем расчетаrow_number()over(partition by q.id order by q.id) rn мы можем получить любую 1 строку из результата соединения.
Значение count(*)over(partition by q.id) qty — для строк фильтра, где все значения из CtriterionTbl соответствуют CSV_vals.
Мы не используем distinct или group by для повторяющихся значений в CriterionVals, поскольку это не влияет на результат.

Демо (на примере @TN)

Или

select *
from(
select q.* 
  ,row_number()over(partition by q.id order by q.id) rn
from QryTable q
inner join CriterionTbl c 
  on concat(',',csv_vals,',') like concat('%,',c.criterionvals,',%')
)t
where rn=(select count(*) from CriterionTbl)

Спасибо всем, @TN предоставил решение, которое я смог применить.

В моем случае я создаю хранимую процедуру, которая будет принимать строку параметров с условиями поиска, разделенными & или +.

Если они разделены знаком &, то каждый поисковый запрос должен быть включен в набор результатов.

Если они разделены знаком +, то в каждый возвращаемый результат должен быть включен один поисковый запрос.

Используя мою упрощенную исходную таблицу в качестве примера, вот полное решение, которое я реализовал:

Create Table QryTable (ID [int], Description [nvarchar](255), Vals_1 [nvarchar](255), Vals_2 [nvarchar](255), Vals_3 [nvarchar](255))
insert into QryTable
values
    (1, 'Description of record #1', 'val_1 val_2 val_3','val_4 val_5 val_6'),
    (2, 'Description of record #2', 'val_1 val_3 val_6','val_9 val_10 val_11'),
    (3, 'Description of record #3', 'val_2 val_3 val_4','val_15 val_20 val_21')
    (4, 'Description of record #4', 'val_2 val_5 val_9','val_15 val_20 val_21')
    (5, 'Description of record #5', 'val_2 val_8 val_5','val_15 val_20 val_21')
    (6, 'Description of record #6', 'val_2 val_6 val_11','val_1 val_20 val_3')

declare @AndOr int = 0
declare @Search_Table table (s_id int IDENTITY(1,1), searchTerm nvarchar(50))
--to return results with any term separated by + 
declare @SearchString nvarchar(255) = 'val_1+val_3+val7'
--to return results with all terms separated by &
declare @SearchString nvarchar(255) = 'val_1&val_3&val7'

IF CHARINDEX('&',@SearchString) > 0
    SET @AndOr =1; SET @SearchString = replace(@SearchString, '&&', '|')
IF CHARINDEX('+',@SearchString) > 0
    SET @SearchString = replace(@SearchString, '++', '|')
    
insert into @Search_Table SELECT    distinct value FROM string_split(@SearchString,'|') ST

if @AndOr = 0
    SELECT  *
    FROM    QryTable QT
    JOIN    @Search_Table ST
    ON  (       QT.Vals_1 LIKE '%' + ST.SearchString + '%'
            OR  QT.Vals_2 LIKE '%' + ST.SearchString + '%'
            OR  QT.Vals_3 LIKE '%' + ST.SearchString + '%'
        )
if @AndOr = 1
    SELECT  *
    FROM    QryTable QT
    WHERE   1=ALL   (
                        SELECT CASE WHEN QT.Vals_1 LIKE '%' + ST.SearchString + '%'
                        OR  QT.Vals_2 LIKE '%' + ST.SearchString + '%'
                        OR  QT.Vals_3 LIKE '%' + ST.SearchString + '%'
                        THEN 1 ELSE 0 END
                        FROM @Search_Table ST
                    )