From: Richard Huxton <dev@archonet.com>
To: Robert Treat <xzilla@users.sourceforge.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: save me from an unconstrained join
Date: Thu, 31 Mar 2005 08:15:17 +0100
Message-ID: <424BA385.4010809@archonet.com> (raw)
In-Reply-To: <1112216088.28936.404.camel@camel>
References: <1112216088.28936.404.camel@camel>
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
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: dev@archonet.com, xzilla@users.sourceforge.net
Subject: Re: save me from an unconstrained join
In-Reply-To: <424BA385.4010809@archonet.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