Re: Another simple query question

Rob Cecil wrote:
>
> Two tables A and B
> B has a one-to-many relationship to A, i.e. has a FK back to A
> B has a single field 'Foo', string type
>
> How would a query be constructed that expresses:
>
> Select all rows in A where A has four related rows in B, with B.Foo
> being 'Black','Yellow','White','Orange'

   I think it should be something like this

   SELECT * FROM a
   WHERE a_id IN

      (SELECT a_id FROM b
       WHERE foo IN ('Black','Yellow','White','Orange')
       GROUP BY a_id
       HAVING COUNT(*) = 4)

Valery Yourinsky

-- 
Oracle8 Certified DBA
Moscow, Russia