Hi Nige,

What about something like this:

SELECT STR_HREF FROM TBL_WORDS W
JOIN TBL_PAGEWORD P ON (W.INT_WORD=P.INT_WORD)
WHERE W.STR_WORD='beer' OR W.STR_WORD='4WD' OR W.STR_WORD='chocolate'
GROUP BY STR_HREF HAVING COUNT(STR_HREF) = 3

This is all purely theoretical - so someone please correct me if I'm wrong
:)

Cheers,

Ryan Thomas
TransActive Systems

P: (02) 4322 3302
F: (02) 4325 1141
E: ryan@...
W: http://www.transactive.com.au/

> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Nigel Weeks
> Sent: Friday, 2 September 2005 10:39 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] There must be a better way.
>
> I'm putting a search facility on our intranet, and I've hit a wall.
> My current idea is:
>
> A table of words, with an integer key
> Create table tbl_words (
> int_word integer not null primary key,
> str_word varchar(100) not null unique
> );
>
>
> A table of word id's, linked to an address
> Create table tbl_pageword (
> int_page integer not null,
> int_word integer not null,
> str_href varchar(200) not null,
> primary key(int_page, int_word, str_href)
> );
>
> The problem is, doing 'and' searches.
>
> For example: find the pages that contain ALL THREE TERMS 'beer',
> 'chocolate', '4WD'
>
> Subselects are slower than I'd like, as a new word lookup has
> to be done for
> each word. For example:
>
> Select str_href from tbl_pageword where
> int_word in (select int_word from tbl_words where str_word = 'beer')
> AND int_word in (select int_word from tbl_words where str_word =
> 'chocolate')
> AND int_word in (select int_word from tbl_words where
> str_word = '4WD');
>
> There has to be a better way. Any ideas? Even a complete
> schema change is
> welcomed.
>
> Nige.
>
>
>
> Nigel Weeks
> Tech Support & Systems Developer
> nweeks@...
> www.examiner.com.au
> 71 - 75 Paterson Street
> Launceston
> Tasmania 7250
> Australia
> Phone: 03 6336 7234
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~-->
> Get Bzzzy! (real tools to help you find a job). Welcome to
> the Sweet Life.
> http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/67folB/TM
> --------------------------------------------------------------
> ------~->
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>