Message-ID: <424BA385.4010809@archonet.com>
Date: Thu, 31 Mar 2005 08:15:17 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Robert Treat <xzilla@users.sourceforge.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: save me from an unconstrained join
References: <1112216088.28936.404.camel@camel>
In-Reply-To: <1112216088.28936.404.camel@camel>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

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