SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    selecting products based on price range and sale price range

    Hi there

    I was hoping for a quick solution to this problem.....

    I'm allowing users of my site to input a price range to filter the product list. Sounds simple but I need to compare the values against either 'prod_price' or 'product_saleprice' but only compare against 'prod_saleprice' if the value is greather than 0. I don't know how or if I can do some sort of if statement within my where clause?

    At the minute my query looks like this:
    Code:
    SELECT
        p.prod_id,
        p.prod_name,
        p.prod_price,
        p.prod_saleprice,
        b.brand_name,
        pp.thumbnail
    FROM products_to_categories AS ptc
        INNER JOIN
            products AS p
            ON p.prod_id = ptc.prod_id
    		AND p.prod_price >= 80
    		AND p.prod_price <= 100
        LEFT JOIN
            brands AS b
            ON b.brand_id = p.brand_id
        LEFT JOIN
            products_photos AS pp
            ON pp.photo_id = p.default_photo_id
    WHERE
        ptc.category_id = 25
    ORDER BY p.prod_price ASC
    LIMIT 0, 9
    This produces incorrect results because a product would be shown if it has a regular price of lets say &#163;85 but a sale price of &#163;60. So how can I adapt it to use 'prod_saleprice' if a value exists or just use 'prod_price' if 'prod_saleprice' is 0?

    Many thanks

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Code SQL:
    SELECT    
         p.prod_id
         ,p.prod_name
         ,p.prod_price
         ,p.prod_saleprice
         ,b.brand_name
         ,pp.thumbnail
      FROM
         products p
     INNER 
      JOIN
         products_to_categories ptc
        ON
         p2c.category_id = 23
      AND
         p.prod_id = ptc.prod_id
      LEFT
      JOIN
         brands b
        ON
         p.brand_id = b.brand_id
      LEFT
      JOIN
         products_photos pp
        ON
         p.prod_id = pp.default_photo_id
     WHERE
          (p.prod_saleprice > 0 AND (p.prod_saleprice BETWEEN 80 AND 100))
          OR
          (p.prod_saleprice <> 0 AND (p.prod_price BETWEEN 80 AND 100))
     ORDER
        BY
         p.prod_price ASC

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Oddz,

    Thanks for the reply. Could you possibly explain how that works as I can't get it to work?

    Perhaps this line:

    (p.prod_saleprice <> 0 AND (p.prod_price BETWEEN 80 AND 100))

    should be:

    (p.prod_saleprice = 0 AND (p.prod_price BETWEEN 80 AND 100))

    ?

    Many thanks

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Yeah, I believe that would be correct for your circumstances.

    Oh… and p2c needs to be ptc. I normally write 2 over t for look up tables.

  5. #5
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again,

    I've been doing a bit more reading into this as I had a feeling I could add some better conditional statements to the WHERE clause. Anyway, I came up with this:
    Code:
    SELECT
        p.prod_id,
        p.prod_name,
        p.prod_price,
        p.prod_saleprice,
        b.brand_name,
        pp.thumbnail
    FROM products_to_categories AS ptc
    INNER JOIN
        products AS p
        ON p.prod_id = ptc.prod_id
        AND
        (
            CASE WHEN p.prod_saleprice > 0
            THEN p.prod_saleprice BETWEEN 2000 AND 2500
            ELSE p.prod_price BETWEEN 2000 AND 2500
            END
        )
    LEFT JOIN
        brands AS b
        ON b.brand_id = p.brand_id
    LEFT JOIN
        products_photos AS pp
        ON pp.photo_id = p.default_photo_id
    WHERE
        ptc.category_id = 25
    ORDER BY p.prod_price ASC
    LIMIT 0, 9
    This seems to work well

    It's possible that only a max or min price could be specified, so all I need to do now is adapt it for if a max or min price isn't specified.

    Many thanks.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would change this --
    Code:
    CASE WHEN p.prod_saleprice > 0
         THEN p.prod_saleprice BETWEEN 2000 AND 2500
         ELSE p.prod_price BETWEEN 2000 AND 2500
     END
    to this --
    Code:
    CASE WHEN p.prod_saleprice > 0
         THEN p.prod_saleprice 
         ELSE p.prod_price 
     END BETWEEN 2000 AND 2500
    furthermore, i'm guessing you use 0 to represent "no sale price"

    i would use NULL here instead, so that the CASE expression can be simplified to --
    Code:
    COALESCE(p.prod_saleprice,p.prod_price)
      BETWEEN 2000 AND 2500
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    Ah yes, that's much cleaner. I was going to try and make the query deal with situations where a min or max price isn't passed to the query. Eg. If only a min price is passed then we check for products that have a price or sale price above that value. But I think this is probably easier and quicker to do with PHP and generate the relevant query accordingly

    Works a treat! Although I'm thinking BETWEEN might not be suitable as the range should probably be inclusive of the min and max values.

    Thanks again!

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sxtrail View Post
    Although I'm thinking BETWEEN might not be suitable as the range should probably be inclusive of the min and max values.
    i don't know where this min and max business came into the picture, but if this is just your terminology for the lower and upper endpoints of a BETWEEN range, let me reassure you that BETWEEN ~does~ include its endpoints

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you sure? As it certainly doesn't appear to work like that. If I run this:
    Code:
    SELECT
        p.prod_id,
        p.prod_name,
        p.prod_price,
        p.prod_saleprice,
        b.brand_name,
        pp.thumbnail
    FROM products_to_categories AS ptc
    INNER JOIN
        products AS p
        ON p.prod_id = ptc.prod_id
        AND
        (
        CASE WHEN p.prod_saleprice > 0
            THEN p.prod_saleprice 
            ELSE p.prod_price 
            END
            BETWEEN 799.98 AND 1099.99
        )
    LEFT JOIN
        brands AS b
        ON b.brand_id = p.brand_id
    LEFT JOIN
        products_photos AS pp
        ON pp.photo_id = p.default_photo_id
    WHERE
        ptc.category_id = 25
    ORDER BY p.prod_price ASC
    LIMIT 0, 9
    ....then I get the products that are priced at 799.99 and 1099.99. If the lower value of the BETWEEN is changed to 799.99 then I don't get the products price at 799.99. So only the upper endpoint appeats to work

    Thanks

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    let me guess... datatype of your price columns is FLOAT?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep Is that a bad thing?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sxtrail View Post
    Yep Is that a bad thing?
    it certainly is a bad thing if 799.99 doesn't equal 799.99

    check da manual, it's quite explicit -- FLOAT is an approximate datatype

    try it with DECIMAL, e.g. DECIMAL(9,2) or something
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    The thing about placing the conditionals iin the ON clause is that most likely it will result in much slower query. You'll need to run an explain though to confirm that. Rather then a simple equality check the conditional will be ran for every single row. Not the most efficient way of doing things. You should also index those columns. Otherwise, your looking at a full table scan plus the conditionals for every single row.


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
  •