List:General Discussion« Previous MessageNext Message »
From:chas Date:April 22 1999 11:15am
Subject:RE: intersection in SQL with only one table ?
View as plain text  
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

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