Re: SQL Question... Boolean Logic

dvick_at_lanier.com (Don Vick) writes:

>In article <ggrieffD7BDn4.84x_at_netcom.com>,
>Greg Grieff <ggrieff_at_netcom.com> wrote:
>>
>>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.
>>
 

>Wouldn't the following give you what you want, or am I missing something?
 

> select distinct docid where keyword='apples' or keyword='oranges';

No, that would return 1 and 2.
What you need is the relational algebra operator 'divide', which ORACLE does not (nor any other commercial DB, TTBOMK) provide.

Try :

select distinct docid
from keywords
where (docid, 2) in (select docid, count(*)

                     from   keywords
                     where  keyword in ('apples', 'oranges')
                     group  by docid)

Note: you must know the number of boolean operands up front - a failing of this mock-divide clone.

--
L. Scott Johnson (sjohnson_at_math.scarolina.edu) 
Graphics Specialist and Jyhad Rules' Monger.