Re: Query help (brain went on strike)
Date: 1996/08/07
Message-ID: <32096599.7333_at_jp.oracle.com>#1/1
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));
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.
Received on Wed Aug 07 1996 - 00:00:00 CEST