Re: restrictive WHERE-IN

In article <19990802151811.24257.00003325_at_ngol02.aol.com>,   paulcinvt_at_aol.com (PaulCinVT) wrote:
> 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
>

Paul,

I ran your query but did not get the described result. I got no row returned.

However, the subquery:

   where person_id in ( select person_id    from table1
   where value_id in ( 1, 2, 3, 4 ))

returns two qualifying person_id: "1" and "2"

Therefore your original query is like:
  select * from table1
   where person_id in (1,2)

Hope helps.

Andrew from SF

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Aug 02 1999 - 17:50:21 CDT