>>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
>

I think this would not work. Did you try it? I tried yesterday in VFP

CREATE cursor MainC ;
(ID_Field I, char_Field c(250))
INSERT INTO MainC VALUES (1,'Mother Father Daughter Son')
INSERT INTO MainC VALUES (2,'Mother Daughter Son')
INSERT INTO MainC VALUES (3,'Mother Son')
INSERT INTO MainC VALUES (4,'Daughter Son')
INSERT INTO MainC VALUES (5,'Mother Father Son')
INSERT INTO MainC VALUES (6,'Son Daughter Father')
INSERT INTO MainC VALUES (7,'Mother Son')
INSERT INTO MainC VALUES (8,'Other Word')

SELECT 0
CREATE CURSOR KeyWords (Word C(20), IF I)
INSERT INTO KeyWords VALUES ('Mother',1)
INSERT INTO KeyWords VALUES ('Farther',2)
INSERT INTO KeyWords VALUES ('Son',3)
INSERT INTO KeyWords VALUES ('Daughter',4)


SELECT M.* FROM MainC M, ;
(SELECT K1.Word as Word1, K2.Word as Word2 from KeyWords K1 ;
INNER JOIN KeyWords K2 ON K1.IF = K2.IF +1) Words ;
WHERE Word1 $ M.Char_Field

This is one of the tries. When I tried to join with Keywords using LIKE clause it always returned one set with 'son' keyword.