Re: HELP! Need to do this Query

Ian McDonald <ian_at_softopt.co.uk> wrote:

>Hi folks,
 

>I can't seem to get my head around this query. I wondered if anyone could offer a
>suggestion.
 

>I have two tables of information as follows:-
 

>People | Combinations
>====== | ============
> |
>Name + Interest | Name + Interest
>--------------+-------------- | --------------+--------------
>Ian + Football | All Sports + Football
>Ian + Cricket | All Sports + Cricket
>Ian + Tennis | All Sports + Tennis
>Tom + Football | Team Sports + Football
>Tom + Cricket | Team Sports + Cricket
>Fred + Football |
 

>I wish the query to work as follows:-
 

>1. Select the 'people.name' from 'people' which have the same interests as
> from 'combinations', 'combinations.name=All Sports'?
> ONLY RETURNS: Ian.

select people.name
  from people, combinations
 where people.interest = combinations.interest (+)    and combinations.name (+) = 'All Sports' group by people.name
having ( count(*), count( combinations.interest ) ) =

	   ( select count(*), count(*)
           from combinations
          where combinations.name = 'All Sports' )

>2. Select the 'people.name' from 'people' which have the same interests as
> from 'combinations', 'combinations.name=Team Sports'?
> ONLY RETURNS: Tom.

select people.name
  from people, combinations
 where people.interest = combinations.interest (+)    and combinations.name (+) = 'Team Sports' group by people.name
having ( count(*), count( combinations.interest ) ) =

	   ( select count(*), count(*)
           from combinations
          where combinations.name = 'Team Sports' )

>If anyone has any ideas, please e-mail me:- ian_at_softopt.co.uk
 

>Thanks in advance.
 

>Ian McDonald

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government