At 01:08 PM 12/03/2005 +0200, you wrote:

>Hi Helen
>
>Thanks for your time and valuable insights :) Very very very much
>appreciated.
>
> > select oi.* from orderitems oi
> > join orderitems_word_match ows
> > on ows.orderitem_id = oi.orderitem_id
> > and exists (
> > select 1 from menu_word_search mws1
> > where mws1.m_w_s = ows.m_w_s
> > and mws1.word = 'PITA' )
> > and exists (
> > select 1 from menu_word_search mws2
> > where mws2.m_w_s = ows.m_w_s
> > and mws2.word = 'CHEESE' )
> > /* keep going if you like */
> > and exists (
> > select 1 from menu_word_search mws3
> > where mws3.m_w_s = ows.m_w_s
> > and mws3.word = 'HUMMUS')
> >
> > Then you can recycle the keywords for use with many tables, by creating
> > just one intersection table for each table that has a searchable text
> > field
> > in it.
>
>This is pretty much the way I have structured the data, but I think my first
>query was waaaaaay off base. The second example you gave works very well,
>with sub second query times. The only problem is that it only works for
>single word matches.
>
>[ORDERITEMS]
>LINK ITEM
>-----------------
>1 CHEESE PITA
>2 CHICKEN PITA
>
>[MENU_WORD_SEARCH]
>LINK WORD
>------------------
>100 CHEESE
>101 PITA
>102 CHICKEN
>
>[ORDERITEMS_WORD_MATCH]
>ORDERITEMLINK WORDSEARCHLINK
>--------------------
>1 100
>1 101
>2 101
>2 102
>
> >> This works
>select oi.* from orderitems oi
> join orderitems_word_match ows
> on ows.orderitemlink = oi.link
>and exists (
> select 1 from menu_word_search mws1
> where mws1.link = ows.wordlink
> and mws1.word = 'PITA' )
>
> >> This doesnt
>select oi.* from orderitems oi
> join orderitems_word_match ows
> on ows.orderitemlink = oi.link
>and exists (
> select 1 from menu_word_search mws1
> where mws1.link = ows.wordlink
> and mws1.word = 'PITA' )
>and exists (
> select 1 from menu_word_search mws1
> where mws1.link = ows.wordlink
> and mws1.word = 'CHEESE' )
>
>Any further help or suggestions will be greatly appreciated,

No, it wouldn't, because it's trying to AND in the same cursor.
Get back to the two-cursor approach but try putting another cursor on
orderitems_word_match to deal with the circularity in your structure, and
some brackets to isolate the two AND-ed join tests:

select oi.* from orderitems oi
join orderitems_word_match ows1
on ows1.orderitemlink = oi.link
and
(exists (
select 1 from menu_word_search mws1
where mws1.link = ows1.wordlink
and mws1.word = 'PITA' ))
join orderitems_word_match ows2
on ows2.orderitemlink = oi.link
and
(exists (
select 1 from menu_word_search mws2
where mws2.link = ows2.wordlink
and mws2.word = 'CHEESE' ))

./h