Don's query is exactly the same as mine, only using slightly different
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.
> 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:
> > s1.docid
> > search s1
> > join search s2
> > s1.docid = s2.docid
> > s1.wordid = 49
> > 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
> search s1
> join search s2
> join search s3
> s1.docid = s2.docid
> s2.docid = s3.docid
> s1.wordid = 49
> s2.wordid = 45
> 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
> 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 :
> 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 :)
> 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