Re: Query help (brain went on strike)

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/08/08
Message-ID: <3209ed93.1678954_at_dcsun4>#1/1

On Wed, 07 Aug 1996 18:21:23 -0700, Jim Kennedy <Jim_Kennedy_at_medicalogic.com> 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 of:

select *
  from words a
 where word in ( 'skin', 'cancer' )
   and exists ( select null

		  from words b
                 where b.id = a.id 
		   and word in ( 'skin', 'cancer' )
                 group by id
                having count(*) = 2 )

/
select a.*
from words a, words b
where a.id = b.id
 and (( a.word = 'skin' and b.word = 'cancer' ) or

      ( a.word = 'cancer' and b.word = 'skin' )) /

select *
  from words a
 where word in ( 'skin', 'cancer' )
   and exists ( select null

		  from words b
		 where b.id = a.id
		   and b.word in ( 'skin', 'cancer' )
		   and b.word <> a.word )

/

would work although I think #3 will be the most performant if you have an index on WORD and a separate index on ID (a unique index on ID, WORD would be great. That way the index on WORD will be used on the outer query to find 'skin' 'cancer', the unique index on ID-WORD will be used in the exists, and the only table access will be to get the "select * from words a..." at the end)

>Any help would be appriciated.
>
>Jim Kennedy
>(jim_kennedy_at_medicalogic.com)

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