Re: save me from an unconstrained join

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