X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8741CD7C22 for ; Tue, 6 Sep 2005 01:38:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 21947-07 for ; Tue, 6 Sep 2005 04:38:45 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id 61F01D7B20 for ; Tue, 6 Sep 2005 01:38:44 -0300 (ADT) Received: (qmail 18924 invoked from network); 6 Sep 2005 04:38:09 -0000 Received: from dsl092-071-205.bos1.dsl.speakeasy.net (HELO ?66.92.71.205?) (66.92.71.205) by 0 with SMTP; 6 Sep 2005 04:38:09 -0000 In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v734) Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed Message-Id: Cc: pgsql-sql@postgresql.org Content-Transfer-Encoding: quoted-printable From: "Thomas F. O'Connell" Subject: Re: Searching for results with an unknown amount of data Date: Mon, 5 Sep 2005 23:38:42 -0500 To: "DownLoad X" X-Mailer: Apple Mail (2.734) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 required=5 tests=[AWL=0.005] X-Spam-Level: X-Archive-Number: 200509/57 X-Sequence-Number: 22604 On Sep 2, 2005, at 2:40 PM, DownLoad X wrote: > Hi, > > I've got two tables, A and B, the first one containing columns > > A_ID | info > > where A_ID is primary, so that this table stores various =20 > information about an object, and the second containing columns > > A_ID | property > > where property is an integer referring to a particular property =20 > that an object may possess. I've seperated these into two tables =20 > so that an object may have several (ie an unknown number of) =20 > properties. > > Now, I want to find all objects that have at most properties 1,2,3, =20= > say (so something with (1,2) is okay, as is (1,2,3)). I can't see a =20= > way to do this -- can anyone help? > Also, what if I want to find all the objects possessing properties =20 > 4,5,6, as well as possibly other things? I've done it with nested =20 > SELECTs (constructed programmatically), but I think this is quite =20 > slow and not a very pleasing solution. > > Obviously, both these things will need to be done for an arbitrary =20 > list of integers. > > Thanks, > DL Without knowing more about your data or schema, couldn't you do =20 something like SELECT A_ID, property FROM "B" WHERE property IN ( 1, 2, 3 ); To accommodate arbitrary lists, I can't imagine how you'd do it =20 without using a PL, but you could still build your IN clause =20 programmatically. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i=99 http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax)=