List:General Discussion« Previous MessageNext Message »
From:Robin Bowes Date:April 22 1999 11:55am
Subject:Re: intersection in SQL with only one table ?
View as plain text  
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
Thread
phpMichael Farr22 Apr
  • Re: phpSasha Pachev22 Apr
  • Re: phpDavor Cengija22 Apr
    • intersection in SQL with only one table ?chas22 Apr
      • RE: intersection in SQL with only one table ?Don Read22 Apr
        • RE: intersection in SQL with only one table ?chas22 Apr
          • RE: intersection in SQL with only one table ?Don Read22 Apr
      • Re: intersection in SQL with only one table ?Robin Bowes22 Apr
        • Re: intersection in SQL with only one table ?Alex Krohn22 Apr
          • Re: intersection in SQL with only one table ?chas23 Apr
  • Re: intersection in SQL with only one table ?Robin Bowes22 Apr
Re: phpPhilip Diller22 Apr