Re: restrictive WHERE-IN

From: PaulCinVT <paulcinvt_at_aol.com>
Date: 02 Aug 1999 19:18:11 GMT
Message-ID: <19990802151811.24257.00003325@ngol02.aol.com>

Start with...

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);

NOW...Add this to the mix...

insert into table1 values(1,5);

Now run this...

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 /

And you get this...

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

         1          1
         1          2
         1          3
         1          5    <------Note!
         1          4

Paul in VT Received on Mon Aug 02 1999 - 14:18:11 CDT