Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5PMbha68949 for ; Mon, 25 Jun 2001 18:37:43 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 77065; Mon, 25 Jun 2001 15:37:39 -0700 From: "Josh Berkus" Subject: Re: select with multiple occurences in same table To: "William Herring" Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Mon, 25 Jun 2001 15:37:39 -0700 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200106/406 X-Sequence-Number: 3703 WIlliam, > Table "inventorytrack" > Attribute | Type | Modifier > ------------+-----------+---------- > tattoo | text | > past_group | text | > ranch | text | > ent_date | timestamp | > indate | date | > Are you sure that you're using the most efficient data types for this table? And where's your primary key? > In the above table, I have multiple occurrences of ‘tattoo’. > However, some > of the tattoo’s have null value in ‘indate’, some do not. I would > like to > select those tattoos with a null indate, unless there is another > identical > tattoo in the table that has a date value in ‘indate’. THis one is easy. However, I'm not sure exactly waht you want; the sample return tuples you provided do not match what you asked for! If you wanted to answer the question above: SELECT * FROM inventorytrack WHERE indate IS NULL AND NOT EXISTS ( SELECT tattoo FROM inventorytrack it2 WHERE it2.tattoo = inventorytrack.tattoo AND indate IS NOT NULL ); -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco