Re: SQL Question... Boolean Logic

In article <ggrieffD7BDn4.84x_at_netcom.com>, ggrieff_at_netcom.com says...
>
>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."
|
>------------------------------------------------------------------------
 -
>

I think if I understand your problem correctly, you could either use a intersect or join on the same table:

The Intersect might be:

select distinct docid from keywords
where keyword = 'apples'
intersect
select distinct docid from keywords
where keyword = 'oranges';

The join would be:

select distinct a.docid
from keywords a, keywords b

where  a.docid = b.docid
and    a.keyword = 'apples'
and    b.keyword = 'oranges'

I think both of these will work...

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