keyword search problem 1

[wjl11]

Hello,

I have a keyword search field which would contains words separated by spaces. (IE: modem pool)

I have a table which contains one row for each keyword entry. IE:
Rec#1 ID=abc word=elbow
Rec#2 ID=abc word=macaroni
Rec#3 ID=abc word=modem
Rec#4 ID=abc word=pooling

Can you suggest a SQL statement that would find all IDs that include the beginning portion of ALL search terms?

Thanks,
Will

 

[swampBoogie]

select id from t
where word like 'modem%' 
   or word like 'pool%'
 group by id
 having count(id) = 2

 

[wjl11]

Thanks!
One problem with this solution is that if there are 2 keywords in the table prefaced with one of the words it thinks all is OK.

IE:
If 'modems' and 'modeming' are present in the table, but no mention of 'pool', the count will still be 2.

Any work around you can think of?

Thanks again,
Will

 

[swampBoogie]

select t1.id 
from t t1, t t2
where t1.word like 'modem%'
  and t2.word like 'pool%'
  and t1.id = t2.id