'LIKE' clause with 'AND' operator in SQL Server 2012

Question

I have exactly the same requirement as in this thread. How to use JOIN LIKE with AND Operator in SQL Server?

I have been clueless despite searching the net for hours.

I have a table 'Filter' with column 'Value' and 2 records in it as 'Value1' and 'Value2' as varchar values.

Select * From MasterTable
Inner Join Filter ON MasterTable.Name LIKE '%' + Filter.Value + '%'

This query means:

Select * From MasterTable
Where MasterTable.Name LIKE '%Value1%' OR MasterTable.Name LIKE '%Value2%'

Now how can I change JOIN LIKE that means AND not OR? Something like:

Select * From MasterTable
Where MasterTable.Name LIKE '%Value1%' AND MasterTable.Name LIKE '%Value2%'

Can anyone show me a way out.

Answer 1

You can generally rewrite a "X matches all Y" query as "X does not have any Y that do not match" - or in other words a NOT EXISTS query for the inverse of Y.

select *
from MasterTable
where not exists (
    select 1
    from Filter
    where MasterTable.Name not like '%' + Filter.Value + '%'
)