Self-join ?

Self-join ?

• stilts

  You don't need a self-join to get this information. You can write a query like below:
  
  select t.Person
  from tbl as t
  where t.Ability in ('See', 'Hear')
  group by t.Person
  having count(*) = 2; -- number of abilities
  -- use count(distinct t.ability) = 2 if ability is not unique per person
  
  The generic way to represent this query is following:
  
  select t.Person
  from tbl as t
  where t.Ability in (<list of items>)
  group by t.Person
  having count(*) = <number of items in list>;
  
  You can also find similar example of this problem at http://www.windowsitpro.com/Article/ArticleID/24792/24792.html.
  
  

• Rid

  Birger,
  
  There are a couple of approaches to this problem, which is
  sometimes called "relational division". One is less flexible
  but a bit easier to grasp:
  
  select
    Person
  from yourTable
  where Ability in ('See','Hear')
  group by Person
  where count(distinct Ability) = 2
  
  If (Person, Ability) is unique, you can remove "distinct"
  from the query.
  
  Another is to select all persons for whom neither of the
  abilities 'See' and 'Hear' is missing:
  
  select -- Select everyone...
    Person
  from yourTable as T1
  where not exists ( -- ...for whom there is no...
    select * from ( -- ...required ability...
     select 'See' as Ability union all
     select 'Hear'
    ) as A
    where not exists ( -- ...not associated with that person.
     select * from yourTable as T2
     where T2.Person = T1.Person
     and A.Ability = T1.Ability
    )
  )
  
  The part with union all can be replaced by a query against
  another table where you might have the desired abilities
  kept.
  
  (I didn't test these to check for typos)
  
  Steve Kass
  Drew University
  
  Konrad_IX@discussions.microsoft.com wrote:
  > Hello all, 
  > 
  > First of i'm not sure whether I post this thread the correct place. If I
  > don't, please forgive me. 
  > 
  > This is possibly a rather simple Q, but nevertheless a challenge for me:
  > 
  > On a single table, I have information about people and their abilities.
  > Each ability is located on it's own row.
  > 
  > Person   Ability
  > -------------------
  >  A      See
  >  A      Hear
  >  B      See
  >  C      See
  >  C      Talk
  > 
  > What I wish to do now, is to make a statement that returns alle the
  > people that can both see and hear (ie. A). 
  > 
  > Someone told me it should be a self-join, but whenever I tried it, it
  > converted the code to a inner join. (Possibly I have done something
  > wrong)
  > 
  > Are suggestions (preferably code examples)
  > 
  > Regards
  > 
  > Birger Wilhelmsen
  > 
  
  
  
  
  
                                              
  

• umahesh2k1

  Hi Birger,
  
  SELECT *
  FROM TABLE
  WHERE Person IN (SELECT Person FROM TABLE WHERE Ability IN ('See', 'Hear'))
  
  

• Magnus Lovsten

  what you are looking for is:
  
  SEE ∩ HEAR
  
  
  For this case, I think using a self join would actually be the most efficient (but the SQL engr's would know better) . . .
  
  Given the table is called people, in sql, SEE ∩ HEAR literally implemented is:
  
  select SEE.person 
  from people SEE inner join people HEAR
     on  SEE.person = HEAR.person
  where  SEE.ability = 'See' and HEAR.ability = 'Hear'
  
  
• Self-join ?