Re: HELP! Need to do this Query

In article <316D24BE.3C9F_at_softopt.co.uk>, ian_at_softopt.co.uk says...
>...
>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.
>...

If I understand your question correctly, you want to select all pairs of people.name and combination.name with exact matching interests.

This can be done by the following

• snip ------------------------------------- select a.name,b.name from people a, combinations b where a.interest = b.interest group by a.name, b.name having count(*)
  • ( select count(*) from combinations c where c.name = b.name ) and count(*)
  • ( select count(*) from people d where d.name = a.name )
• snap -------------------------------------

hth

-- 
    _/_/_/   _/_/_/ _/    _/  // Reinhard Kuhn             /  It can be      
   _/    _/ _/     _/  _/    //         (kuhn_at_cas-ps.com) /  done quickly,   
  _/_/_/   _/_/_/ _/_/      // CAS GmbH                  /  cheaply or well  
 _/  _/   _/     _/  _/    // Lemberger Strasse 14      /   - pick any two!  
_/   _/  _/_/_/ _/    _/  // 66955 Pirmasens, Germany  /