Re: NOT IN subquery problem.

From: Isa <isapmon_at_terra.es>
Date: Mon, 19 Mar 2001 17:55:37 GMT
Message-ID: <tKrt6.13845$H32.377333@telenews.teleline.es>

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.
>
>
Received on Mon Mar 19 2001 - 11:55:37 CST