I'm trying to look up a value on a lookup table based on an amount and
a date

Now the "lookup-tables" change from period to period so the main
"tables" record has a start and end date and id

SELECT table_id FROM tables
$date >= startdate AND $date <= enddate

next I need to select the right row from the "tablelist" table (which
has all the lookup data) based on a column item that is the maximum
value but less than or equal to the amount


SELECT * FROM tablelist
WHERE table_id = $table_id
AND amount = SELECT MAX(amount) WHERE amount <= $testamount

I think I'm close, how would I roll it all into one?

On 1 Apr, 21:19, la...@portcommodore.com wrote:
> I'm trying to look up a value on a lookup table based on an amount and
> a date
>
> Now the "lookup-tables" change from period to period so the main
> "tables" record has a start and end date and id
>
> SELECT table_id FROM tables
> $date >= startdate AND $date <= enddate
>
> next I need to select the right row from the "tablelist" table (which
> has all the lookup data) based on a column item that is the maximum
> value but less than or equal to the amount
>
> SELECT * FROM tablelist
> WHERE table_id = $table_id
> AND amount = SELECT MAX(amount) WHERE amount <= $testamount
>
> I think I'm close, how would I roll it all into one?

Could you post some create table statements and some sample data in
the form of extended inserts with the expected output from said sample
data?