Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: Problem With Null Values Using Least Statement

    I have a shop on my website which uses prices from various online merchants and I want to use a query which will display the lowest price for each individual product.

    The columns are as follows:
    `ID`, `Name`, `Kitbag Price`, `Subside Sports Price`, `Sports Direct Price`, `Amazon Price`, `Asda Price`, `Play Price`

    Not all products are available from all six merchants, so there are some NULL values in pretty much every row in the table.

    All the price fields are of 'Decimal' type with a 4,2 setting, with PHP code converting them to (GBP) in the front end.

    Below are 3 rows of example data.
    PHP Code:
    `ID`, `Name`    , `Kitbag Price`, `Subside Sports Price`, `Sports Direct Price`, `Amazon Price`, `Asda Price`, `Play Price`
    `
    1` , `Product 1`, `9.99`, `12.99`, `NULL`, `15.99`, `NULL`, `NULL`
    `
    2` , `Product 2`, `NULL`, `NULL`, `13.99`, `NULL`, `12.50`, `12.99`
    `
    3` , `Product 3`, `12.99`, `11.99`, `11.99`, `NULL`, `NULL`, `9.99
    Below is my query which should get the lowest value in that row, but because of the NULL values, it is coming up with a NULL result.

    PHP Code:
    SELECT *, LEAST(`Kitbag Price`, `Subside Sports Price`, `Sports Direct Price`, `Amazon Price`, `Asda Price`, `Play Price`) FROM `productsORDER BY `products`.`IDASC 
    I have also tried changing the price columns from 'Decimal' to 'VARCHAR', leaving the NULL fields empty, but using row 1 as an example, the result would display as 12.99 because of the leading '1' character, when the actual result should display as 9.99.

    Sorry for the long post but I hope this explains everything and gives somebody the chance to help me solve the problem!

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You don't have the best design of database as it's difficult to expand ie add another merchant etc or add another pricing currency. This design is also making it difficult for you to pick the lowest price. The decimal 4,2 might work at the moment but if exchange rates continue their downward trend (or you start to source goods from China, India or Japan etc) then it just won't be enough. It's also nasty using spaces in your field names as it means you need a million quotes everywhere. One possible better design would something like:
    Code:
    create table Prices(
        prod_id     int,
        merchant_id int,
        price      decimal(4,2),
        price_date datetime,
        currency char(3),
        primary key (prod_id, merchant_id)
    )
    Then you could just do the following code for any number of merchants:
    Code:
    select min(price)
    from Prices
    where prod_id = 123
    As it stands you'll just need to add ifnull around each price ie :
    Code:
    SELECT *, LEAST( ifnull(`Kitbag Price`,99999), 
        ifnull(`Subside Sports Price`,99999), 
        ifnull(`Sports Direct Price`,99999), 
        ifnull(`Amazon Price`,99999), 
        ifnull(`Asda Price`,99999), 
        ifnull(`Play Price`,99999) ) 
    FROM `products` 
    ORDER BY `products`.`ID` ASC

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    since you are still able at this point in your site development to change the design of the table, i urge you most strenuously to get rid of those 6 columns and add a new table instead, one which consists of the product id, the source id, and the price

    then your query problems disappear

    (also, you might want to increase DECIMAL(4,2) to maybe (5,2) -- you'll have to do this sooner rather than later anyway, in case prices go over 99 bucks, er, i mean, pounds)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2009
    Posts
    2
    Thanks for your answers guys. The problem is now sorted and I changed from (4,2) to (5,2) although it's very unlikely that the extra digit will ever be needed.

    The main sales will be replica merchandise which usually retails at about 40.

    Thanks again!

Posting Permissions

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