Re: Assessing items by keywords

In article <2tmp4e$4jv_at_euas20.eua.ericsson.se> joe_at_erix.ericsson.se (Joe Armstrong) writes:
> I'd like to be able to describe various articles by a number of
>keywords:
>
> Example:
>
> Keywords
> ========
> C, OS/2, Compliers -> Article1
> C++, Objects, Microsoft -> Article2
> ....
>
> Then I'd like to be able to search
>
> "Find all objects with keywords C, Compilers"
>
> 123 hits
> ...
>
> etc.
>
> What's the best way to represent this in SQL?
>

These may not be the *best* ways, but I believe them to be *some* ways.

I assume you have a table like this

      create table articles (title      char(80),
                             author     char(40),
                             publication
                             ...
                             keywords long);

      select count(*)
      from ARTICLES
      where instr(ARTICLES.KEYWORDS,'&keyword1') > 0 
        and instr(ARTICLES.KEYWORDS,'&keyword2') > 0;
      

The above SQL statement, when submitted via SQL*Plus will prompt for two values, keyword1 and keyword2. It will then retrieve the count of articles that contain both keywords. Unfortunately, it will find any article that has the keyword as an embedded string.

If you delimited your keywords with a special character that is never part of a valid keyword you could employ the following strategy.

If for example KEYWORDS contained the string

      !C!Compilers!OS/2!

one could issue the SQL statement:

      select count(*)
      from ARTICLES
      where instr(ARTICLES.KEYWORDS,'!&keyword1!') > 0 
        and instr(ARTICLES.KEYWORDS,'!&keyword2!') > 0;
      

In the above examples, Oracle will *NOT* be able to use indexes. To do so one may want to do something like this:

      create table articles (article_nb number,
                             title      char(80),
                             author     char(40),
                             publication ...
      
      create table keywords (article_nb number,
                             keyword char(40));
      

Then to get a count of articles you could issue a SQL statement similar to:

      select count(*)
      from articles 
      where article_nb in
           (select article_nb
            from keywords
            where keyword = '&keyword1'
            intersect
            select article_nb
            from keywords
            where keyword = '&keyword2')
      

or to get a list of articles:

      select title, author, ...
      from articles 
      where article_nb in
           (select article_nb
            from keywords
            where keyword = '&keyword1'
            intersect
            select article_nb
            from keywords
            where keyword = '&keyword2')

Good luck! I sould appreciate hearing how this works for you.

-- 
============================================================================
Michael Sallwasser  | Down one path is utter dispair and hopelessness. Down 
Northrop Grumman    | the other is total destruction. Let us choose wisely.
============================================================================