SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Nov 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select with range

    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

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Welcome to the SP forums.
    If I understood your requirements correctly, this might be the solution
    Code:
    WHERE minAge < 8 
    AND   maxAge > 3

  3. #3
    SitePoint Member
    Join Date
    Nov 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Welcome to the SP forums.
    If I understood your requirements correctly, this might be the solution
    Code:
    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

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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
    Code:
    where 7 between minAge and maxAge

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by itischarles View Post
    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

    Code:
    WHERE minAge < 2 
    AND   maxAge > 0
    gives you exactly what you asked for. In generic form:
    Code:
    WHERE minAge < $theMaxAgeUserInput 
    AND   maxAge > $theMinAgeUserInput
    Last edited by guido2004; Nov 1, 2012 at 07:30. Reason: eliminated copy&paste error

  6. #6
    SitePoint Member
    Join Date
    Nov 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    By the way

    Code:
    WHERE minAge < 2 
    AND   maxAge > 0
    gives you exactly what you asked for. In generic form:
    Code:
    WHERE minAge < $theMaxAgeUserInput 
    AND   maxAge > $theMinAgeUserInput
    gives you exa

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •