Re: Searching for results with an unknown amount of data

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)