Date: Wed, 13 Aug 2003 20:12:24 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Abdul Wahab Dahalan <wahab@mimos.my>
Cc: Christoph Haller <ch@rodos.fzk.de>, <pgsql-sql@postgresql.org>
Subject: Re: How to speeed up the query performance
In-Reply-To: <3F3AF940.9000002@mimos.my>
Message-ID: <20030813201031.D67777-100000@megazone.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 14 Aug 2003, Abdul Wahab Dahalan wrote:

> Hai Chris!
> Thanks for the solution but seem it doesnt work.
> (0 rows) returned when I used NOT EXITS but (4 rows) returned
> when NOT IN is used...................

Maybe you need a set of parenthesis around the old conditions
because of the or.

> >Try:
> >select
> >ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber
> >
> >from    transportsetup ts
> >where   ts.bizid = 'B126'
> >and     ts.deletestatus = 0
> >and     NOT EXISTS (    select t.transportid
> >        from transportsetup t,servicedetail s,logisticservice l
> >        where ts.transportid = t.transportid

> >            and t.bizid=l.bizid
Replace the above with:
	       and (t.bizid=l.bizid

> >            and l.serviceid=s.serviceid
> >            and t.transportid=s.transportid
> >            and t.bizid = 'B126'
> >            and l.status='Pending'
> >        or t.bizid=l.bizid
> >            and l.serviceid=s.serviceid
> >            and t.transportid=s.transportid
> >            and t.bizid = 'B126' and l.status='Reserved' )

Add:
		)

> >order by ts.transporttype;