Re: restrictive WHERE-IN

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 28 Jun 1999 12:59:33 GMT
Message-ID: <377770c3.1626859@newshost.us.oracle.com>

A copy of this was sent to "Joerg Leute" <leute_at_itdesign.de> (if that email address didn't require changing) On Sat, 26 Jun 1999 19:49:08 +0200, you wrote:

>Hi everybody
>
>Is there any way to have a restrictive where-in query?
>
>Table1:
>person_id value_id
>1 1
>1 2
>1 3
>1 4
>2 1
>
>If i now
>SELECT * FROM PERSON WHERE VALUE_ID IN (1,2,3,4)
>i get person 1 and 2
>
>is there any way to return person 1 only, because person 1 has every value.
>
>Thanks in advance
>
>Joerg
>

SQL> create table table1(person_id int,value_id int); Table created.

SQL> insert into table1 values(1,1);
SQL> insert into table1 values(1,2);
SQL> insert into table1 values(1,3);
SQL> insert into table1 values(1,4);
SQL> insert into table1 values(2,1);

SQL> select *
  2 from table1
  3 where person_id in ( select person_id

  4                          from table1
  5                         where value_id in ( 1, 2, 3, 4 )
  6                         group by person_id
  7                        having count( distinct value_id ) = 4 )

  8 /

 PERSON_ID VALUE_ID
---------- ----------

         1          1
         1          2
         1          3
         1          4

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 28 1999 - 07:59:33 CDT