Re: NOT IN subquery problem.

Neat.

On Mon, 19 Mar 2001 17:55:37 GMT, "Isa" <isapmon_at_terra.es> wrote:

>Hi!
>You can do the next:
>
> select t.supervisor_id
> from emp1 t
> where 0=(select count(*)
> from user u, userrole rm
> where u.userid = rm.userid and
> u.emp_id = t.supvisor_id ):
>Regards,
>
>
>ISA
>
>
>"PasirRis" <jackling_at_singnet.com.sg> escribió en el mensaje
>news:995c8m$56m$1_at_clematis.singnet.com.sg...
>> version: Oracle 8.1.7 on Solaris 8.
>>
>> Hi,
>>
>> I've a query:
>>
>> select t.supervisor_id
>> from emp1 t
>> where t.supvisor_id NOT IN
>> (select u.emp_id from user u, userrole rm
>> where u.userid = rm.userid);
>>
>> INTENTION:
>> - want to show records which appear in emp1 tbl but not in the result list
>> from the subquery.
>>
>> GIVEN:
>> 1. I'm sure there is a record in emp1 table the supervisor_id of which
 does
>> not appear (as emp_id) in the subquery.
>>
>> 2. the subquery, (select u.emp_id from user u, userrole rm
>> where u.userid = rm.userid) correctly shows the expected result.
>>
>> 3. If I modify the query to IN clause (instead of NOT IN), the result
>> behaves correctly, i.e. only show those matches.
>>
>> 4. supervisor_id in emp1 table and emp_i in user table has the same
>> datatype.
>>
>> QUESTION:
>> - but I don't know why it returns no rows (in the NOT IN clause where
 there
>> should be a least some records in emp1 not appearing in the subquery
 result
>> list)
>> - Is my quey constructed correctly to get my expected result?
>>
>> I've even modified to save the results in a working tempory table, say,
 emp2
>> tbl. Then, I modify the query to say .... NOT IN (select emp_id from
 emp2).
>> But this does not help either.
>>
>> I really don't know what's wrong here.
>> Pls highlight to me.
>> thanks,
>> jackling.
>>
>>
>
>

EMail: knijff_at_bigfoot.com Received on Tue Mar 20 2001 - 12:42:46 CST