Select records from a table, where the results match all criterion in a sub-query

Example...

Create Table QryTable (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')

Create Table CriterionTbl (ID [int] identity (1,1), CriterionVals [nvarchar](50))
insert into CriterionTbl values ('val_3', 'val_4')

Functionality similar to:

Select *
from QryTable
where CSV_vals like `AND (SELECT CriterionVals from CriterionTbl)`

The results would be the same as if the query were written, except the independent values are the results of querying the CriterionVals table, and only records which include all values listed in the CriterionVals table are returned, rather than records which include any of the values in the CriterionVals table:

Select *
from QryTable
where CSV_vals like '%val_3%'
and  CSV_vals like '%val_4%'

I'm able to use the following query to get records with any value in the CriterionTbl, but I need to write a query which returns results of records that have all criterion from the CriterionTbl sub-query.

This is what I have that returns records with anything in the CriterionTbl:

select *
from QryTable
Join (select * from CriterionTbl as CT)
on QryTable.CSV_Vals like '%' + CT.CriterionVals + '%'

This returns: records 1, 2, and 3, becauuse 1,2, and 3 all have either 'val_3' or 'val_4' But what I want is only records 1 and 3, because only 1 and 3 have 'val_3' and 'val_4'

But I need this to happen from the sub-query, so that the results are based on the contents of CriterionVals table.

Usually, an any-criteria condition would be applied using WHERE EXISTS(SELECT * FROM Criteria WHERE criteria-passes) and an all-criteria condition can be applied using WHERE NOT EXISTS(SELECT * FROM Criteria WHERE NOT criteria-passes).

For this case, I believe you need the latter:

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

I added the commas to the above test to avoid false matches between strings like "val_2" and "val_20".

The ALL() operator can also be used, with a bit of coaxing and a CASE expression that maps a success or fail to a 1 or 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
)

It is worth noting that if CriterionTbl is empty, the condition will always be true.

The results are the same for both of the above:

See this db<>fiddle for a demo. (Thanks to @BartMcEndree for the initial fiddle set-up.)