Re: Query help (brain went on strike)

On Wed, 07 Aug 1996 20:57:13 -0700, Ken Mizuta <kmizuta_at_jp.oracle.com> wrote:

>Jim Kennedy wrote:
>>
>> I have two tables.
>>
>> Table prob has two fields: description (character) and id (integer).
>>
>> Table words has two fields: id (integer) and keyword.
>>
>> The purpose of the prob table is to hold a list of medical problems (described).
>> The purpose of the words table is to hold a list of words (0 to N rows for each row in prob)
>> that are related to a prob. Thus someone could search on cancer in the words table and get back a
>> list of problems from the prob table. (thru the id field)
>>
>> I have that query.
>>
>> What if I want to get back a list of problems that are both cancer and skin. I do not want rows
>> from the words table that have cancer or skin. I want rows that have the same id and cancer or
>> skin. Thus,
>>
>> id keyword
>> 1 cancer
>> 1 skin
>>
>> if the words table looks like thus
>> id keyword
>> 1 cancer
>> 1 skin
>> 1 heme
>> 2 skin
>> 3 cancer
>>
>> Any help would be appriciated.
>>
>> Jim Kennedy
>> (jim_kennedy_at_medicalogic.com)
>
>
>Try this:
>
>SELECT p1.id id, p1.keyword keyword
> FROM prob p1, prob p2
> WHERE p1.id=p2.id and
> ((p1.keyword='cancer' and p2.keyword='skin') ||
> (p1.keyword='skin' and p2.keyword='cancer));

Think you meant OR not || (too much c coding I assume :).....

>
>This should get you exactly what you are looking for.
>
>--
> ____________________________________________
> / Kenichi Mizuta
> //// / Oracle Corporation (Redwood Shores, CA)
> |0 0| / Applications Division
>_ooO_ \U/_Ooo_/ email: kmizuta_at_us.oracle.com
>The comments and opinions expressed herein are mine and
>do not necessarily represent those of Oracle Corporation.

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Thu Aug 08 1996 - 00:00:00 CEST