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


I didn't said you can do this with the same query in VFP :-)
Try this in SQL Server:

DECLARE @MyTable TABLE (Id int, Searched varchar(200))
DECLARE @Keys    TABLE (Word varchar(200), Id int)

INSERT INTO @MyTable VALUES (1,'Mother Father Daughter Son')
INSERT INTO @MyTable VALUES (2,'Mother Daughter Son')
INSERT INTO @MyTable VALUES (3,'Mother Son')
INSERT INTO @MyTable VALUES (4,'Daughter Son')
INSERT INTO @MyTable VALUES (5,'Mother Father Son')
INSERT INTO @MyTable VALUES (6,'Son Daughter Father')
INSERT INTO @MyTable VALUES (7,'Mother Son')
INSERT INTO @MyTable VALUES (8,'Other Word')


INSERT INTO @Keys VALUES ('Mother',1)
INSERT INTO @Keys VALUES ('Father',2)
INSERT INTO @Keys VALUES ('Son',3)
INSERT INTO @Keys VALUES ('Daughter',4)

DECLARE @nAllWords int
SELECT @nAllWords = COUNT(*) FROM @Keys

SELECT MyTable.*
FROM @MyTable MyTable
INNER JOIN (SELECT MyTable.Id
                   FROM @MyTable MyTable
            INNER JOIN @Keys KeyWords ON MyTable.Searched LIKE '%'+KeyWords.Word +'%'
            GROUP BY MyTable.Id
            HAVING COUNT(*) = @nAllWords) Tbl1 ON MyTable.Id = Tbl1.Id

BTW in your example you wrote "FaRther: in KeyWords cursor :-)