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.