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..