Received: from sowrong.org (syr-24-24-23-149.twcny.rr.com [24.24.23.149]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2D2Eax22453 for ; Mon, 12 Mar 2001 21:14:38 -0500 (EST) (envelope-from cjc26@sowrong.org) Received: (from cjc26@localhost) by sowrong.org (8.11.0/8.11.0) id f2D2EL017368; Mon, 12 Mar 2001 21:14:21 -0500 (EST) Date: Mon, 12 Mar 2001 21:14:21 -0500 From: Cliff Crawford To: Alder Cc: pgsql-sql@postgresql.org Subject: Re: SQL Dummy Needs Help Message-ID: <20010312211421.A17214@cornell.edu> References: <3aa9287b@obsidian.gov.bc.ca> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i 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/243 X-Sequence-Number: 1902 * Alder menulis: > 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. It's probably better to store them as type date instead. That way you can use functions like date_part() to extract the month and day. You could use string functions to extract, say, the last 4 characters to get the MMDD value, but that can get messy if someone accidentally stores a date in YYMMDD format. > 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. > > Sorry I can't explain this a little more technically, but if anyone can > advise me, that would be fabulous. If you change your tables to store the dates as type date instead of type string, then you can do something like the following: SELECT table2.title_no, table2.paidto_date FROM table1, table2 WHERE table1.title_no=table2.title_no AND (date_part('month', table1.effect_date) != date_part('month', table2.paidto_date) OR date_part('day', table1.effect_date) != date_part('day', table2.paidto_date)); (At least, I think that will work, but I never trust any SQL I write without thoroughly testing it first..;) -- Cliff Crawford He who sacrifices his conscience to ambition burns a picture to obtain the ashes. - Chinese proverb