Keyword SQL Query

Question

I currently have an SQL query that returns results based on the dynamic number of keywords passed.

I convert a list of keywords into a table and join it.

    SELECT * FROM Table1
    INNER JOIN
        dbo.udf_List2Table(@Keywords, ',') ON (Field1 LIKE '%'+Keyword+'%')

This works fine, but returns all rows containing any keywords to be grouped. What I would like to do is return all rows containing all of the provided keywords.

I am sure you cannot do this using JOIN. Does anyone have any suggestions on how I can do this? I am trying to avoid dynamic SQL.

thank

Answer 1

SELECT  *
FROM    Table1
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    dbo.udf_List2Table(@Keywords, ',')
        WHERE   field1 NOT LIKE '%' + Keyword + '%'
        )

, FULLTEXT field1 @Keywords AND:

SET @Keywords = '"cabbages" AND "kings"'
SELECT  *
FROM    table1
WHERE   CONTAINS(Field1, @Keywords)

Answer 2

Try

SELECT Field1 FROM Table1
INNER JOIN
    dbo.udf_List2Table(@Keywords, ',') ON (Field1 LIKE '%'+Keyword+'%')
GROUP BY Field1
HAVING COUNT(Keyword) = n

n

Answer 3

You may be able to make the following settings.

DECLARE @recordcount int
SELECT @recordcount = count(1) from dbo.udf_List2Table(@Keywords, ',')
select t1.Field1
from Table1 t1
where @recordcount = (select count(1) from dbo.udf_List2Table(@Keywords, ',') k where t1.Field1 like '%' + k.Keyword + '%')