Yes, Milan, this was a very efficient query you suggested!!! Even on my tiny set of less than 20 records my query took 0.110 secs. and your suggestion took it down to 0.016 secs. with a lot of improvement in memory and other usages.

Thanks a lot.
Regards
Bhavbhuti

--- In firebird-support@yahoogroups.com, Milan Babuskov <milanb@...> wrote:
>
> Bhavbhuti Nathwani wrote:
> > This is what I have created, can it be made better?
> > SELECT mItems.iID, mItems.cDesc
> > FROM mItems
> > WHERE (mItems.iID NOT IN (SELECT DISTINCT mParent.iID
> > FROM mItems mChild
> > JOIN mItems mParent
> > ON mParent.iID = mChild.iPID))
> > OR mItems.lExcisable = 1
>
> Try:
>
> SELECT iID, cDecs
> FROM mItems m
> WHERE lExcisable = 1 OR not exists(
> select 1
> from mItems d
> where d.iPID = m.iID)
>
>
> HTH
>
> --
> Milan Babuskov
> http://www.flamerobin.org
> http://www.guacosoft.com
>