itischarles — 2012-11-01T02:55:45-04:00 — #1
URGENT HELP I have a table of products with age range on them.
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.
"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)
guido2004 — 2012-11-01T03:40:10-04:00 — #2
Welcome to the SP forums.
If I understood your requirements correctly, this might be the solution
WHERE minAge < 8
AND maxAge > 3
itischarles — 2012-11-01T05:25:27-04:00 — #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.
guido2004 — 2012-11-01T06:52:17-04:00 — #4
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 — 2012-11-01T06:55:01-04:00 — #5
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 — 2012-11-01T09:26:49-04:00 — #6
You are a star. it worked great.
tanks for your time