Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Location
    Ahmedabad
    Posts
    4

    Unanswered: Query to select range

    I have two tables in my database.
    Code:
    First Table Name: product_price_range
    Field                          Type             
    -----------------------------  
    price_category_id              int(11)    
    title                          varchar(155)  
    min_price                      float          
    max_price                      float       
    
    
    Data: product_price_range
    price_category_id  title        min_price  max_price 
    -----------------  -----------  ---------  --------- 
                    1  1-49                 1         49 
                    2  50-99               50         99 
                    3  100-199            100        199 
                    4  200-299            200        299 
                    5  300-erboven        300          0 
    
    Second Table: product
    Field               Type             
    -----------------------------  
    product_id           int(11)    
    title                varchar(155)  
    price                float          
    
    
    Data: product
    product_id  title            price  
    ---------- -----------  ---------
               1  product1       49 
               2  product2       99 
               3  product3       149 
               4  product4       249 
               5  product5       300
    Now I want to show the only those price range in which product price is available. For that I need a query or other possible way to identify those price range.
    In above example output should be
    1. 1-49
    2. 50-99
    4. 200-299

    Can anyone help me out in this issue?

    Regards,
    Amit Shah

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT DISTINCT ppr.title
      FROM product_price_range AS ppr
    INNER
      JOIN product AS p
        ON p.price BETWEEN ppr.min 
                       AND CASE WHEN ppr.max = 0 
                                THEN 9999999999
                                ELSE ppr.max END
    the join would've been cleaner if you hadn't used 0 as the max price

    also, be careful using FLOATs -- you really should be using INTEGER for these prices

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Location
    Ahmedabad
    Posts
    4
    Thanks for query. It is working fine..

Posting Permissions

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