How to pull specific records in SQL

[jeep2001]

FAM TYPE
--- ----
CAT BO
CAT MM
CAT IP
DOG MM
CAR BO
CAR MM


I am trying to construct a query where I can pull out the FAM where the TYPE is "BO" OR "MM". For example, I would not want the CAT record with the TYPE "IP" and I would not want the DOG record with the "MM" type but I would want the CAR record. Essentially I want all FAM's that have a TYPE that is "BO" and "MM"...

any help would be wonderful, Thanks

 

[r937]

select FAM
  from daTable
group
    by FAM
having 2 
     = sum(iif(TYPE in ('BO','MM'),1,0))
   and 2 
    <= count(*)

this will also return FAMs which have exactly two BOs or exactly two MMs, but if FAM,TYPE is a composite primary key, that won't happen

r937.com | rudy.ca