Re: restrictive WHERE-IN

This is the expected result:
You subquery says:

    get all the rows where the value_id is in 1,2,3,4     group by person id and count them

        person id will get exactly (1,1), (1,2), (1,3), (1,4) even when (1,5) exists

        counting these four rows gets the value 4     return only the rows where the count is 4 So person_id 1 is passed to the outer query.

    which returns all the rows for person_id = 1     viz: your result.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

PaulCinVT wrote in message <19990802151811.24257.00003325_at_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 Tue Aug 03 1999 - 03:25:00 CDT