Database Field - Range (mySQL)

[richkoi]

I would like to know the best way to set up a database so a query can easily search for a range of data. For example, my current mySQL database is as follows:



Using this table I would like to create a query for a plant that would be between 30-40 feet. This would display only the Bambusa oldhamii plant.

1) Is there a better way to set up the database for this? If so, how? Also, what would the query be?

2) Using the existing format, what would be the best way to query this for a range? Is it even possible?

Please let me know if you need clarification.

Thanks,

Rich

[brian.mark]

select * from planttable where (Ht_Min >= 30 and Ht_Min <= 40) OR (Ht_Max >= 30 and Ht_Min <= 30);

I think that should do what you were asking. Ranges, especially when 2 variables are involved, can get confusing.

Brian.

[richkoi]

Thanks, I'll try that query out.

Rich

[richkoi]

Here is another suggestion I got:

Rich
I think I have a query that will catch all the possibilities.

The inputs are X=Low Y=High

SELECT * FROM plant_table WHERE (Ht_max BETWEEN X AND Y) OR (Ht_min BETWEEN X AND Y) OR (Ht_min <= X AND Ht_max >= Y)

from the examples you would get
X Y
20 30 The first 'between' gets Oldhamii and the 2nd 'between' gets Aurea
25 60 Both 'between' statements get Oldhamii
35 50 The last 'AND' statement gets Oldhamii