Thread: save me from an unconstrained join

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
integerNOT 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

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