Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2DFvmx44030 for ; Tue, 13 Mar 2001 10:57:48 -0500 (EST) (envelope-from reedstrm@rice.edu) Received: by rice.edu via sendmail from stdin id (Debian Smail3.2.0.102) for pgsql-sql@postgresql.org; Tue, 13 Mar 2001 09:57:41 -0600 (CST) Date: Tue, 13 Mar 2001 09:57:41 -0600 From: "Ross J. Reedstrom" To: Alder Cc: pgsql-sql@postgresql.org Subject: Re: SQL Dummy Needs Help Message-ID: <20010313095741.A17879@rice.edu> Mail-Followup-To: Alder , pgsql-sql@postgresql.org References: <3aa9287b@obsidian.gov.bc.ca> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0i In-Reply-To: <3aa9287b@obsidian.gov.bc.ca>; from Terrence.Branscombe@gems8.gov.bc.ca on Fri, Mar 09, 2001 at 11:05:38AM -0800 X-Archive-Number: 200103/263 X-Sequence-Number: 1922 On Fri, Mar 09, 2001 at 11:05:38AM -0800, Alder wrote: > I'm pretty new to SQL and can't figure out how to write what should be a > simple query of two tables. Could someone here possibly help me out? > > Table 1 has two fields I'm interested in: TITLE_NO and EFFECT_DATE > Table 2 also has two fields of interest: TITLE_NO and PAIDTO_DATE > > TITLE_NO is unique in Table 1, meaning each TITLE will have a unique > EFFECT_DATE. Table 2 represents an accounting history of each TITLE, so for > each TITLE_NO there may be one or more PAIDTO_DATE values. In both Tables > the _DATE fields are stored as 9-character strings in the fomat YYYYMMDD. > In all cases, the MM and DD values in Table 2 should be identical with those > in Table 1. > > My intention is to produce a list that will contain the TITLE_NO of each > TITLE where the MMDD value for EFFECT_DATE differ from any of the > PAIDTO_DATE values for that TITLE_NO. The list must contain ONLY the > PAIDTO_DATE values that differ, and the corresponding TITLE_NO. I've seen a couple answers to this, but I think people aren't getting the question right. I'm presuming that the desired return values are the EFFECT_DATEs without matching PAIDTO_DATEs, rather than returning PAIDTO_DATEs, as stated, but that's just tweaking the return set. It seems to me you're looking for something like: select * from table1 t where not exists (select * from table2 where t.title_no = title_no and t.effect_date = paidto_date) Ross