SQL Question... Boolean Logic

From: Greg Grieff <ggrieff_at_netcom.com>
Date: 1995/04/20
Message-ID: <ggrieffD7BDn4.84x_at_netcom.com>#1/1

Well I'm beginning to think that this is not possible, hopefully someone will prove me wrong...

I have the following table:

create table keywords (
   recid        number(9)    not null,  /* primary key */
   docid        number(9)    not null,  /* foreign key to another table */
   keyword      varchar2(30) not null,  /* keyword entry */

)

Sample data:

   recid docid keyword

---

    1          1         apples
    2          1         oranges
    3          2         apples
    4          2         pears
    5          2         watermelon

I want to return the unique values for docid that match boolean logic combinations of keyword values. For example, if I ask for all vaules for docid that have keywords, apples and oranges, I get the answer 1. I would like standard order of operations to apply to the values that I search for in keywords. And, (this is the difficult part) I would like a single SQL (non-PL/SQL) statement that does this.

   My best attempt was to make a corrolated subquery as follows:

select unique docid from keywords a where keyword in

   (select keyword from keywords b where a.docid = b.docid)    and keyword = 'apples' and keyword = 'oranges';

this quite obviously won't work since the keyword for a particular row can't possibly equal oranges and apples. I suspect that the secret to this lies in the clever use of UNION or INTERSECTION but I'm not really familiar with either of these concepts.

Any help you can give me on this would be greatly appreciated, or if perhaps you know it is not possible to do this let me know that too.

• Thanks in advance
• Greg

  ---

| Greg Grieff ggrieff_at_netcom.com | "Dream, Design, Develop, Debug, |
| Head of Engineering | Deliver... Not necessarily in |
| Micrographic Specialties Inc. | that order." |

---

Received on Thu Apr 20 1995 - 00:00:00 CEST