select with range

[itischarles]

URGENT HELP I have a table of products with age range on them.

minAge maxAge
0 6
2 4
3 5
7 11
8 11

i wan to select from products where the age fall within the ages of 3 to 8
I am expecting all the products except the last to show up since some of the ages with 3- 8 are found in the table age range

solution1 "select from products where min >= 3 AND maxAge <= 8.

this worked but prod1 andprod2 are filtered out of the result. I deally since 3 is within the min and max age, it should be included in the result.

soln 2
"select * from products where (minage between 3 AND 8) AND (maxage <= 8 )

again prod1 is filtered out. I want product 1 since ages 3,4,5,6 are within the search string (3-8)

any ideas

[guido2004]

Welcome to the SP forums.
If I understood your requirements correctly, this might be the solution

WHERE minAge < 8 
AND   maxAge > 3

[itischarles]

Welcome to the SP forums.
If I understood your requirements correctly, this might be the solution

WHERE minAge < 8 
AND   maxAge > 3

Thanks for your reply. I have tried that before but it does not seem to work correctly. I have a db of products. These products have ages of people they apply to. an item can be worn by some one from ages 0 to 4, 3-8, 5-11, 0- 2, etc. when i search for an item that can be worn by people whose ages fall between 0 to 2, i expect to get the items : 0-4, 0-2 listed above.
However, i tried
select .. minage >= 0 and maxage <= 2

the 0-4 gets filtered out because the condition says <= 2.

any ideas

[guido2004]

I understand the products have age ranges.
What I don't understand is why you use two different ages in your search. A client (or the child they want to buy clothes for) doesn't have an age range. He as an age.

For example 7 years old.
Then the search would become

where 7 between minAge and maxAge

[guido2004]

Thanks for your reply. I have tried that before but it does not seem to work correctly. I have a db of products. These products have ages of people they apply to. an item can be worn by some one from ages 0 to 4, 3-8, 5-11, 0- 2, etc. when i search for an item that can be worn by people whose ages fall between 0 to 2, i expect to get the items : 0-4, 0-2 listed above.
However, i tried
select .. minage >= 0 and maxage <= 2

the 0-4 gets filtered out because the condition says <= 2.

any ideas

By the way

WHERE minAge < 2 
AND   maxAge > 0

gives you exactly what you asked for. In generic form:

WHERE minAge < $theMaxAgeUserInput 
AND   maxAge > $theMinAgeUserInput

[itischarles]

By the way

WHERE minAge < 2 
AND   maxAge > 0

gives you exactly what you asked for. In generic form:

WHERE minAge < $theMaxAgeUserInput 
AND   maxAge > $theMinAgeUserInput

gives you exa

You are a star. it worked great.
tanks for your time