Path: news.easynews.com!easynews!sjcppf01.usenetserver.com!usenetserver.com!cyclone.bc.net!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: milbratz@hotmail.com (william milbratz)
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.tools
Subject: Re: HELP! Select Stmt
Date: 10 Oct 2001 18:55:18 -0700
Organization: http://groups.google.com/
Lines: 41
Message-ID: <cee3515e.0110101755.5717c847@posting.google.com>
References: <9q2ftd$b3e$2@coco.singnet.com.sg>
NNTP-Posting-Host: 24.19.42.81
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1002765318 18988 127.0.0.1 (11 Oct 2001 01:55:18 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 11 Oct 2001 01:55:18 GMT
Xref: easynews comp.databases.oracle.misc:70168 comp.databases.oracle.tools:45392
X-Received-Date: Wed, 10 Oct 2001 19:49:11 MST (news.easynews.com)

>I need to find out all records in Table A which do not have any record
> in table B for each operation date. eg 1/9/01 till 30/9/01.

try this:
select * from a 
where where not exists 
(select * from b where b.vehicle_no = a.vehicle_no 
  and b.operation_date between to_date('1999-09-01') and to_date('1999-09-30')
)

kowho@singnet.com.sg (Ho) wrote in message news:<9q2ftd$b3e$2@coco.singnet.com.sg>...
> I have two table :
> 
> 1. Table A 
> 
> 	Vehicle no.
> 	......
> 
> 2. Table B
> 
> 	Vehicle no.
> 	Operation Date 
> 	......
> 
> 
> I need to find out all records in Table A which do not have any record
> in table B for each operation date. eg 1/9/01 till 30/9/01.
> 
> I have written the following stmt :
> 
> Select Vehicle_no from A minus Select Vehicle_no from B where
> operation date='01/09/01'
> 
> The above stmt have to be run 30 times and operation date is not shown
> as a column.
> 
> I have tried Select a.Vehicle_no , b.operation_date from A  minus
> Select Vehicle_no from B where operation date='01/09/01'
> but it does not work ?
> 
> Many thanks for your help.