Re: Subquery not returning results

From: William Robertson <williamr2019_at_googlemail.com>
Date: Fri, 10 Apr 2009 12:28:55 -0700 (PDT)
Message-ID: <b0eb034b-6ff3-4d7f-bfe9-9c4be26d8eaf_at_z14g2000yqa.googlegroups.com>

On Apr 8, 4:05 pm, Sashi <small..._at_gmail.com> wrote:
> Hi All,
>
> I have a simple subquery:
>
> select ckt_id, charges
> from table A
> where ckt_id not in (select distinct ckt_id from B) and charges > 0
>
> I know of at least one record in A whose ckt_id is not present in
> table B and charges > 0 which is not being returned.
>
> Table A has about 15000 records and B has about 50000 records.
>
> What am I missing here?
>
> Thanks in advance,
> ZSashi

Just to follow on from what Mark said about null values, try:

select ckt_id, charges
from table A
where ckt_id not in

       ( select b.ckt_id from b
         where  b.ckt_id is not null )

and charges > 0;

This is a bit of a guess in the absence of complete details, though. Received on Fri Apr 10 2009 - 14:28:55 CDT