Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

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
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 1 2007
Added on Aug 30 2007
31 comments
19,602 views