Hi..
I have a table PROJECT with data as
SQL> select person, id_state from project order by  1, 2;
PERSON       ID_STATE
---------- ----------
Carl                3
Carl                4
Joe                 3
Mark                2
Mark                3
Mark                4
Paul                1
Paul                2
Sam                 2
Sam                 3
10 rows selected.
I want to select persons from this table having all the id states (2, 3 and 4)
In this case the result would be Mark.
The following quiery works
SQL> select person from project t3,
  2  (
  3  select person p2, id_state from project t2,
  4  (
  5  select person p1 from PROJECT where ID_STATE = 2
  6  ) t1
  7  where t1. p1 = t2.person and t2.id_state = 3
  8  ) t4
  9  where t3.person = t4.p2 and t3.id_state = 4
 10  /
PERSON
----------
Mark
But I think this is too large a quiery for such a simple select. Can any one post a more refined and compact quiery for this simple select.
Thanks all in advance..