[ic] Re: UNSOLVED: Database range-lookup question, CommonAdjust?

[interchange-users@icdevgroup.org (marc.brevoort)]

On Friday 06 September 2002 18:05, you wrote:
> On Thursday 05 September 2002 09:30 pm, you wrote:
> > >I want to make a change so that the exact weight of the items can be
> > > used to make a range lookup in the table, for example:
> > >
> > >weight	zone1
> > >
> > >60	0.27
> > >100	0.41
> > >150	0.57
> > >200	0.72
> > >
> > >A weight of 1 to 60 costs 0.27, a weight of 61 to 100 costs 0.41, and so
> > > on.

You can save yourself a lot of trouble by making the information in your
records explicit, and not depend on the contents of other records... like 
that you need to find only one record to get the information that you need.

If I were you I'd make them stand-alone, something like the following:

weight_from	weight_to	zone1
1		60		0.27
60		100		0.41
100		150		0.57
150		200		0.72

This would also prevent you from usingn a data value as key for the table.

The lookup query for weight would simply be something like

select zone1 
from weight_table 
where 
:weight between weight_from and weight_to

(assuming :weight is a parameter of the SQL query)

or if you want to be more certain about what is included 
and excluded in the weight range, 

select zone1 
from weight_table 
where :weight>weight_from 
and :weight <= weight_to

Just my two cents,

Regards,

Marc Brevoort