>Hi everybody,
>
>I found yesterday a very interesting problem. Suppose, we have Keywords table and we need to search in our main table against these keywords to only include records which have all keywords in them. I was playing with it yesterday in VFP and I could not write a query in VFP to achieve it (I suspect we can do this with UDF, but I haven't figured this out).
>
>For SQL Server I have this idea, which I haven't yet tried
>

>declare @KeyWordsCount as int 
>select @KeyWordsCount = count(*) from Keywords
>with KeywordSearchCTE
>as
>(select * from myTable M INNER JOIN Keywords K on M.FieldToSearch like '%' + K.Search_Word + '%'
> UNION ALL
> select * from myTable M INNER JOIN Keywords K on M.FieldToSearch like '%' + K.Search_Word + '%' where K.Search_Word 
> NOT IN (select Search_Word from KeywordSearchCTE))
>
>select M.FieldToSearch, count(*) as cnt_Occurs from myTable M 
>inner join KeywordSearchCTE K on M.ID = K.ID group by 1 having cnt_Occurs = @KeyWordsCount
>
>

>
>Do you think I'm going in the right direction?
>
>Thanks in advance.

declare @KeyWordsCount as int 
select @KeyWordsCount = count(*) from Keywords
SELECT MyTable.*
FROM MyTable
INNER JOIN (SELECT MyTable.PK
                   FROM MyTable
            INNER JOIN KeyWords ON M.FieldToSerach LIKE '%'+KeyWords.Search_Word +'%'
            GROUP BY MyTable.Pk
            HAVING COUNT(*) = @KeyWordsCount) Tbl1 ON MyTable.Pk = Tbl1.Pk