'intersection' SQL

I have created a table ( word c(10), page n(5) ) thats contains all
the words in a book and the page number.

I have an index on 'word'.

I have picked 3 words:

w1="world"
w2='health"
w3="manager"

I need a SQL command that will return a result table (page n5) of all
the pages that contains ALL of the 3 words. (fully optimized is my
goal).

(my current solution starts by 'select'ing out 3 tables with the
intermediate 'page' number tables, but this is not very efficient.)

Can someone think of a good SQL to accomplish this 'intersection' SQL.

THX
Mike Farnesi

swdev2
Wed Nov 05 23:28:59 CST 2003

How about this one ?

select page from tablename into cursor curs_results where word in
("world","health","manager")

lemme know? mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:excel-info@efgroup.net
Free Satellite Receivers and installation ->
http://www.vmcsatellite.com/?aid=58456
mySql / VFP / MS-SQL
"Mike Farnesi" <mfarnesi@hotmail.com> wrote in message
news:26d24e2e.0311052039.4f5e9d7b@posting.google.com...
> I have created a table ( word c(10), page n(5) ) thats contains all
> the words in a book and the page number.
>
> I have an index on 'word'.
>
> I have picked 3 words:
>
> w1="world"
> w2='health"
> w3="manager"
>
> I need a SQL command that will return a result table (page n5) of all
> the pages that contains ALL of the 3 words. (fully optimized is my
> goal).
>
> (my current solution starts by 'select'ing out 3 tables with the
> intermediate 'page' number tables, but this is not very efficient.)
>
> Can someone think of a good SQL to accomplish this 'intersection' SQL.
>
> THX
> Mike Farnesi

quan
Thu Nov 06 02:05:55 CST 2003

Hi,William Sanders,
I think that yr method won't work, Mike Farnesi not means that, I think we
can do it by the following method:

Select X.page from tablename X where Exist (Select * from tablename y on
x.page=y.page and y.word="world") and;
Exist (Select * from tablename Z on X.page=Z.page and Z.word="health")
and;
Exist (Select * from tablename T on x.page=T.page and T.word="manager")
into cursor result_curs

Does this work?
wish u luck!
Chuen

"swdev2" <wsanders.bob@bob.efgroup.com> дÈëÓʼþ
news:uM2cKrCpDHA.2216@TK2MSFTNGP12.phx.gbl...
> How about this one ?
>
> select page from tablename into cursor curs_results where word in
> ("world","health","manager")
>
> lemme know? mondo regards [Bill]
> --
> William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
> email.
> FREE LONG DISTANCE -> mailto:excel-info@efgroup.net
> Free Satellite Receivers and installation ->
> http://www.vmcsatellite.com/?aid=58456
> mySql / VFP / MS-SQL
> "Mike Farnesi" <mfarnesi@hotmail.com> wrote in message
> news:26d24e2e.0311052039.4f5e9d7b@posting.google.com...
> > I have created a table ( word c(10), page n(5) ) thats contains all
> > the words in a book and the page number.
> >
> > I have an index on 'word'.
> >
> > I have picked 3 words:
> >
> > w1="world"
> > w2='health"
> > w3="manager"
> >
> > I need a SQL command that will return a result table (page n5) of all
> > the pages that contains ALL of the 3 words. (fully optimized is my
> > goal).
> >
> > (my current solution starts by 'select'ing out 3 tables with the
> > intermediate 'page' number tables, but this is not very efficient.)
> >
> > Can someone think of a good SQL to accomplish this 'intersection' SQL.
> >
> > THX
> > Mike Farnesi
>
>

Leonid
Thu Nov 06 05:34:13 CST 2003

select t1.page from mytable t1 inner join mytable t2 on =
t1.page=3Dt2.page ;
inner join mytable t3 on t1.page=3Dt3.page ;
where t1.word=3D"world" and t2.word=3D"health" and =
t3.word=3D"manager"

But if you want full optimization, you probably need also index on page

Leonid


"Mike Farnesi" <mfarnesi@hotmail.com> wrote in message =
news:26d24e2e.0311052039.4f5e9d7b@posting.google.com...
> I have created a table ( word c(10), page n(5) ) thats contains all
> the words in a book and the page number.
>=20
> I have an index on 'word'.
>=20
> I have picked 3 words:
>=20
> w1=3D"world"
> w2=3D'health"
> w3=3D"manager"
>=20
> I need a SQL command that will return a result table (page n5) of all
> the pages that contains ALL of the 3 words. (fully optimized is my
> goal).
>=20
> (my current solution starts by 'select'ing out 3 tables with the
> intermediate 'page' number tables, but this is not very efficient.)
>=20
> Can someone think of a good SQL to accomplish this 'intersection' SQL.
>=20
> THX
> Mike Farnesi

mfarnesi
Thu Nov 06 10:51:46 CST 2003

This works! THX Leonid

But I have a question about optimization. Would the order of the
words make any difference? In other words if the first word was the
one most likely to return the LEAST number of hits? etc.

Anders
Thu Nov 06 19:33:39 CST 2003

Hi Mike

SELECT page FROM Words WHERE word in ('health','world','manager') GROUP BY
page HAVING COUNT(*)=3
or
CREATE CURSOR searched (word c(10))
INSERT INTO searched VALUES ('world')
INSERT INTO searched VALUES ('manager')
INSERT INTO searched VALUES ('health')
SELECT page FROM Words W JOIN Searched S ON W.word=S.word ;
GROUP BY W.page HAVING count(*)=3

-Anders




"Mike Farnesi" <mfarnesi@hotmail.com> wrote in message
news:26d24e2e.0311052039.4f5e9d7b@posting.google.com...
> I have created a table ( word c(10), page n(5) ) thats contains all
> the words in a book and the page number.
>
> I have an index on 'word'.
>
> I have picked 3 words:
>
> w1="world"
> w2='health"
> w3="manager"
>
> I need a SQL command that will return a result table (page n5) of all
> the pages that contains ALL of the 3 words. (fully optimized is my
> goal).
>
> (my current solution starts by 'select'ing out 3 tables with the
> intermediate 'page' number tables, but this is not very efficient.)
>
> Can someone think of a good SQL to accomplish this 'intersection' SQL.
>
> THX
> Mike Farnesi

David
Thu Nov 06 19:54:54 CST 2003

Mike,

if the table only contains distinct words per page:

select pageno, count(*) as cnt ;
from tablex ;
into cursor allwords ;
where tablex.word in ( 'world', 'health', 'manager' )
group by pageno ;
having cnt = 3

this also works if your words you are looking for are in a cursor:

select pageno, count(*) as cnt ;
from tablex ;
into cursor allwords ;
where tablex.word in ( select word from LookingForWords )
group by pageno ;
having cnt = reccount( "LookingForWords" )


--
df - Microsoft MVP FoxPro http://www.geocities.com/df_foxpro

"Mike Farnesi" <mfarnesi@hotmail.com> wrote in message
news:26d24e2e.0311052039.4f5e9d7b@posting.google.com...
> I have created a table ( word c(10), page n(5) ) thats contains all
> the words in a book and the page number.
>
> I have an index on 'word'.
>
> I have picked 3 words:
>
> w1="world"
> w2='health"
> w3="manager"
>
> I need a SQL command that will return a result table (page n5) of all
> the pages that contains ALL of the 3 words. (fully optimized is my
> goal).
>
> (my current solution starts by 'select'ing out 3 tables with the
> intermediate 'page' number tables, but this is not very efficient.)
>
> Can someone think of a good SQL to accomplish this 'intersection' SQL.
>
> THX
> Mike Farnesi

Leonid
Fri Nov 07 02:06:35 CST 2003

Hi, Mike

Sorry, but I don't know answer to your question. It seems to me that =
there must not be any significant difference. If you can test it on your =
data, it will be interesting to know. And it is interesting to compare =
the speed of my method with ones posted by Anders and David (in both =
cases: with index on page and without it)

Leonid

"Mike Farnesi" <mfarnesi@hotmail.com> wrote in message =
news:26d24e2e.0311060851.33dc1642@posting.google.com...
> This works! THX Leonid
>=20
> But I have a question about optimization. Would the order of the
> words make any difference? In other words if the first word was the
> one most likely to return the LEAST number of hits? etc.

mfarnesi
Fri Nov 07 12:50:08 CST 2003

Hi Anders:

These SQL commands seem to return ANY of the 3 words, rather than
pages with ALL of the 3 words. (Each command is returning a different
number of records for me).

Trey
Fri Nov 07 13:25:45 CST 2003

if the words are mixed case (some "World", some "world", etc.), make sure
the index on word is Upper() or Lower() and use the same case in the query:
this will get all cases. or just make all the entries the same case. :)
include the distinct in the count to dismiss any places where one of the
words appears 2+x but one doesn't appear at all (e.g., 3 "worlds" none of
the others)

the below gives a partial optimization (as reported using sys(3054)
the only way i could get full optimization was to include an index on
Deleted() [e.g., index on deleted() tag deltag]

e.g., if index on Lower(word)

select pageno, count(distinct word) as cnt ;
from wordtable ;
where inlist(lower(word), 'world', 'health', 'manager') ;
group by pageno ;
having cnt=3 ;
into cursor results

"Mike Farnesi" <mfarnesi@hotmail.com> wrote in message
news:26d24e2e.0311052039.4f5e9d7b@posting.google.com...
> I have created a table ( word c(10), page n(5) ) thats contains all
> the words in a book and the page number.
>
> I have an index on 'word'.
>
> I have picked 3 words:
>
> w1="world"
> w2='health"
> w3="manager"
>
> I need a SQL command that will return a result table (page n5) of all
> the pages that contains ALL of the 3 words. (fully optimized is my
> goal).
>
> (my current solution starts by 'select'ing out 3 tables with the
> intermediate 'page' number tables, but this is not very efficient.)
>
> Can someone think of a good SQL to accomplish this 'intersection' SQL.
>
> THX
> Mike Farnesi

Anders
Fri Nov 07 18:45:54 CST 2003

Not in my tests. Dis you miss HAVING COUNT(*) = 3?

-Anders

"Mike Farnesi" <mfarnesi@hotmail.com> wrote in message
news:26d24e2e.0311071050.737c4b20@posting.google.com...
> Hi Anders:
>
> These SQL commands seem to return ANY of the 3 words, rather than
> pages with ALL of the 3 words. (Each command is returning a different
> number of records for me).