Problem With Null Values Using Least Statement

Manny89 · #1 (**permalink**) 03-16-09, 08:51

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 `products` ORDER BY `products`.`ID` ASC

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!

mike_bike_kite · #2 (**permalink**) 03-16-09, 11:02

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

r937 · #3 (**permalink**) 03-16-09, 11:08

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)

Manny89 · #4 (**permalink**) 03-16-09, 17:05

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech