Ilan Ilan - 3 months ago 10
SQL Question

Using values of table in the WHERE clause of SQL query

I have a table with descriptions of a lot of companies.

TABLE dbo.[TblCompanies]
(
[CompanyID] [int] IDENTITY(1,1) NOT NULL,
[CompanyProfile] [nvarchar](max) NOT NULL
)


Descriptions look like that


"Agilent Technologies, Inc. provides application focused solutions to
.."


I also have another table that has some strings

TABLE dbo.[TblFilter]
(
[Pattern] [nvarchar](100) NOT NULL
)


For example


technology

applications


How can I get
CompanyIDs
of a all the companies that have ALL the values that are in
TblFilter
in their
CompanyProfile
? The appearing order of the filter strings in
CompanyProfile
IS NOT important. What IS important is that ALL these strings will be in the
CompanyProfile
.

Would be prefer solution without cursors.

Thanks.

Answer

You could filter out rows for which any pattern does not match:

select  *
from    TblCompanies c
where   not exists
        (
        select  *
        from    TblFilter f
        where   c. CompanyProfile not like '%' + f.Pattern + '%'
        )
Comments