From: Thomas F. O'Connell <tfo@sitening.com>
To: DownLoad X <x_download@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Searching for results with an unknown amount of data
Date: Mon, 5 Sep 2005 23:38:42 -0500
Message-ID: <BB5DECA2-8495-485B-B113-417F6DB9E40F@sitening.com> (raw)
In-Reply-To: <BAY101-F2270E5778C3FE6208B3501FCA30@phx.gbl>
References: <BAY101-F2270E5778C3FE6208B3501FCA30@phx.gbl>
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
> information about an object, and the second containing columns
>
> A_ID | property
>
> where property is an integer referring to a particular property
> that an object may possess. I've seperated these into two tables
> so that an object may have several (ie an unknown number of)
> properties.
>
> Now, I want to find all objects that have at most properties 1,2,3,
> say (so something with (1,2) is okay, as is (1,2,3)). I can't see a
> way to do this -- can anyone help?
> Also, what if I want to find all the objects possessing properties
> 4,5,6, as well as possibly other things? I've done it with nested
> SELECTs (constructed programmatically), but I think this is quite
> slow and not a very pleasing solution.
>
> Obviously, both these things will need to be done for an arbitrary
> list of integers.
>
> Thanks,
> DL
Without knowing more about your data or schema, couldn't you do
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
without using a PL, but you could still build your IN clause
programmatically.
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tfo@sitening.com, x_download@hotmail.com
Subject: Re: Searching for results with an unknown amount of data
In-Reply-To: <BB5DECA2-8495-485B-B113-417F6DB9E40F@sitening.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox