Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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,807 views