pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSearching for results with an unknown amount of data
5+ messages / 4 participants
[nested] [flat]
* Searching for results with an unknown amount of data
@ 2005-09-02 19:40 DownLoad X <x_download@hotmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: DownLoad X @ 2005-09-02 19:40 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Searching for results with an unknown amount of data
@ 2005-09-02 21:27 Oz <x_download@hotmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Oz @ 2005-09-02 21:27 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Searching for results with an unknown amount of data
@ 2005-09-06 04:38 Thomas F. O'Connell <tfo@sitening.com>
parent: DownLoad X <x_download@hotmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Thomas F. O'Connell @ 2005-09-06 04:38 UTC (permalink / raw)
To: DownLoad X <x_download@hotmail.com>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Searching for results with an unknown amount of data
@ 2005-09-06 14:45 Bruno Wolff III <bruno@wolff.to>
parent: DownLoad X <x_download@hotmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Bruno Wolff III @ 2005-09-06 14:45 UTC (permalink / raw)
To: DownLoad X <x_download@hotmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Searching for results with an unknown amount of data
@ 2005-09-07 21:54 DownLoad X <x_download@hotmail.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 5+ messages in thread
From: DownLoad X @ 2005-09-07 21:54 UTC (permalink / raw)
To: bruno@wolff.to; +Cc: pgsql-sql
> > 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-09-07 21:54 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-02 19:40 Searching for results with an unknown amount of data DownLoad X <x_download@hotmail.com>
2005-09-06 04:38 ` Thomas F. O'Connell <tfo@sitening.com>
2005-09-06 14:45 ` Bruno Wolff III <bruno@wolff.to>
2005-09-07 21:54 ` DownLoad X <x_download@hotmail.com>
2005-09-02 21:27 Searching for results with an unknown amount of data Oz <x_download@hotmail.com>
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