Re: SQL Query Help?

Thanks for the help Huber ! I'll give this a whirl, but it looks like just what I needed. Also, thanks to the rest of you who replied. I had forgotten you could reference a column based on a numerical id as well as name. . .Doh! (slap forhead here).
Thanks again,
Mike

Huber Heinz wrote:

> Mike Briggs wrote:
> >
> > This is probably a newbie question, but it has me stumped.
> >
> > Basically, I want to build a small search engine to cover our
> > bibliography database. Each article is linked to n keywords (usually
> > about 12). The keywords are not prioritized or weighted in any way.
> > The users need to be able to enter their search criteria as keywords,
> > and I need to return the articles which best match their search
> > criteria. Simple, right?
> >
> > So, I built simple two-column table linking the unique key of each
> > article (there are about 30,000) to each of it's keywords. Now, how do
> > I search this table? If the user is looking for "stuff", "junk" and
> > "nonsense", I would like to generate a result set which shows all
> > articles matching at least one keyword, ORDERED BY THE NUMBER OF
> > KEYWORDS MATCHED, desc.
> >
> > It's the part in caps that has me stumped. Any suggestions? There's
> > got to be some way of using a group by, and count to get this, right?
> > Every search engine in the world has solved this, and I'm starting to
> > feel pretty stupid, even though I've been building database applications
> > for years.
>
> Given the following table:
>
> CREATE TABLE Keywords (
> ArticleID INTEGER,
> KeyWord CHAR(20),
> PRIMARY KEY (ArticleID, KeyWord) );
>
> You write the following statement:
>
> SELECT ArticleID, COUNT(*)
> FROM Keywords
> WHERE KeyWord = 'stuff' OR KeyWord = 'junk' OR KeyWord = 'nonsense'
> ....
> GROUP BY ArticleID
> ORDER BY 2;
>
> Heinz
Received on Thu Jan 20 2000 - 00:00:00 CET