Re: SQL Query Help?
Date: 2000/01/20
Message-ID: <3886B134.D81CB716_at_elbanet.co.at>#1/1
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