From: tkyte@us.oracle.com (Thomas J. Kyte)
Subject: Re: Query help (brain went on strike)
Date: 1996/08/08
Message-ID: <3209ed93.1678954@dcsun4>#1/1
references: <32094113.341F@medicalogic.com>
organization: Oracle Corporation
reply-to: tkyte@us.oracle.com
newsgroups: comp.databases.oracle



On Wed, 07 Aug 1996 18:21:23 -0700, Jim Kennedy <Jim_Kennedy@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@medicalogic.com)

Thomas Kyte
Oracle Government
tkyte@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