Skip to Main Content

Newbie: Simple select with 'IN ALL'

578885Aug 30 2007 — edited Sep 22 2007
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..
Comments
Post Details
Added on Aug 30 2007
31 comments
19,478 views