Don's query is exactly the same as mine, only using slightly different
syntax.
Substitute a for s1 and b for s2 and you're there...
You might like to check the archives for more on this issue - I seem to
remember a similar technique that allows the results to be "scored" ie
return the results ordered by the number of words that match but I don't
recall the details.
R.
chas wrote:
>
> Don Read wrote :
> >select a.docid from search as a, search as b
> > where a.docid=b.docid and (a.wordid=45 and b.wordid=49);
>
> wow... I'm pretty much speechless Don - that worked a treat
> and was so succinct; you just made several script functions
> redundant :)
>
> however, I tried using this technique for intersecting 3 or 4
> words and kept getting empty sets. (I'll have to play around with
> it a little more).
>
> That's when I also saw the mail from Robin Bowes :
> >The usual way of doing this is:
> >
> >select
> > s1.docid
> >from
> > search s1
> > join search s2
> >where
> > s1.docid = s2.docid
> >and
> > s1.wordid = 49
> >and
> > s2.wordid = 45
>
> This is easier for me to get my head around and seems to work
> well for the 3 word intersection :
>
> select s1.docid
> from
> search s1
> join search s2
> join search s3
> where
> s1.docid = s2.docid
> and
> s2.docid = s3.docid
> and
> s1.wordid = 49
> and
> s2.wordid = 45
> and
> s3.wordid = 1
> ;
>
> Or perhaps I've just been lucky so far and got coincidental results
> so far.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> Also from Don Read :
> >Also you might want to checkout the SOUNDEX() functions for your
> >word-id's.
>
> This was the first time I'd even heard of SOUNDEX() - sorry, I'm
> not strong at DB's - so i dug through the archives; also, using
> the exceptionally-accurate search engine infoseek, i found :
>
> http://www.hpl.lib.tx.us/clayton/soundex.html
> http://physics.nist.gov/cuu/Reference/soundex.html
> http://www.outfitters.com/genealogy/what-soundex.html
>
> Now, since my search engine is for Chinese as well as English text,
> I guess this is probably not appropriate. Right ?
>
> But, even if it were all English, would it really be advisable to
> use soundex() ? Doesn't it lead to inaccurate results due to the
> number of words that could be soundex'ed to the same value ?
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> Anyway, returning to the original question - thanks again to Robin
> and Don... much, much appreciated :)
>
> Cheers,
>
> chas
>
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread2237@stripped
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.
--
Robin Bowes - System Development Manager - Room 405A
E.O.C., Overseas House, Quay St., Manchester, M3 3HN, UK.
Tel: +44 161 838 8321 Fax: +44 161 835 1657
