Re: Query help (brain went on strike)

From: Ken Mizuta <kmizuta_at_jp.oracle.com>
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

Original text of this message