pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsave me from an unconstrained join
2+ messages / 2 participants
[nested] [flat]
* save me from an unconstrained join
@ 2005-03-30 20:54 Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 1 reply; 2+ messages in thread
From: Robert Treat @ 2005-03-30 20:54 UTC (permalink / raw)
To: pgsql-sql
It actually does what I want... but it offends my database
sensibilities... :-)
Heres the basics of the tables involved:
CREATE TABLE bds_filesize (
bds_filesize_id serial
name text NOT NULL,
byte_limit integer NOT NULL,
slots integer NOT NULL
);
CREATE TABLE software (
software_binary_id serial,
binary_file oid,
filename text,
filesize integer,
checksum text
);
query:
select
software_binary_id, min(byte_limit)
from
bds_filesize, software_binary
where
byte_limit > filesize GROUP BY software_binary_id;
Basically each software is assigned a "class" based on the size of its
binary into a predetermined range of classes that are defined as
relative filesizes. The above query really does work... but istm I ought
to be joining those tables somehow... any ideas?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: save me from an unconstrained join
@ 2005-03-31 07:15 Richard Huxton <dev@archonet.com>
parent: Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2005-03-31 07:15 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-sql
Robert Treat wrote:
> It actually does what I want... but it offends my database
> sensibilities... :-)
>
>
> Heres the basics of the tables involved:
>
> CREATE TABLE bds_filesize (
> bds_filesize_id serial
> name text NOT NULL,
> byte_limit integer NOT NULL,
> slots integer NOT NULL
> );
>
>
> CREATE TABLE software (
> software_binary_id serial,
> binary_file oid,
> filename text,
> filesize integer,
> checksum text
> );
>
>
> query:
>
> select
> software_binary_id, min(byte_limit)
> from
> bds_filesize, software_binary
> where
> byte_limit > filesize GROUP BY software_binary_id;
>
>
> Basically each software is assigned a "class" based on the size of its
> binary into a predetermined range of classes that are defined as
> relative filesizes. The above query really does work... but istm I ought
> to be joining those tables somehow... any ideas?
But you are joining them - via bds_filesize.byte_limit and
software.fileszie. Now, it's not an equality test, but there's nothing
wrong with that.
You could probably do something clever with subqueries rather than using
min() but it would only complicate the query afaics.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-03-31 07:15 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-30 20:54 save me from an unconstrained join Robert Treat <xzilla@users.sourceforge.net>
2005-03-31 07:15 ` Richard Huxton <dev@archonet.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