Thread: Searching for results with an unknown amount of data

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

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)

On Fri, Sep 02, 2005 at 20:40:24 +0100, DownLoad X <x_download@hotmail.com> wrote:
> 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?

It sounds like you are trying to find all objects that do not have any
properties outside of a specific list. One way to get that list is:

SELECT a_id FROM a   WHERE     NOT EXISTS       (SELECT 1         FROM b         WHERE           b.a_id = a.a_id
  AND           b.property NOT IN (1, 2, 3)       )
 
;

This doesn't take into account the semantics of nulls. If your data can have
nulls in it, then you need to decide precisely what you want and adjust
the query appropiately.

> > 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?
>
>It sounds like you are trying to find all objects that do not have any
>properties outside of a specific list. One way to get that list is:

That's exactly right.

>
>SELECT a_id
>   FROM a
>     WHERE
>       NOT EXISTS
>         (SELECT 1
>           FROM b
>           WHERE
>             b.a_id = a.a_id
>             AND
>             b.property NOT IN (1, 2, 3)
>         )
>;

Yupp, this appears to do it! The 'double negation' is very clever.

Thanks a lot,
DL