Returning another value if preferred value is null

Question

SQL noob here struggling with a query...

I have two tables, one that contains product information (ItemCode, ItemName) and one that contains different price lists (up to 10) for each product.

In plain English, this is what I want to achieve:

Select T0.ItemCode, T0.ItemName, T1.Price

Take the price from price list 6: if price in price list 6 is null, then take the price from price list 1 and deduct 5%

It seems like it should be fairly straight forward but I'm not really sure where to begin to be honest I'd be grateful for some advice.

Many thanks, Michael

Show some table data if possible please. – Captain Kenpachi Jun 19 '13 at 11:07 — Captain Kenpachi, Jun 19 '13 at 11:07

score 1 · Answer 1 · answered Jun 19 '13 at 11:00

Here is a way of doing this with two joins, one for each price list:

 select pi.ItemCode, pi.ItemName,
        coalesce(pl6.price, pl1.price*0.95) as price
 from ProductInformation pi left outer join
      PriceList pl6
      on pi.ItemCode = pl6.ItemCode and pi6.List = 6 left outer join
      PriceList pl1
      on pi.ItemCode = pl1.ItemCode and pi1.list = 1;

This assumes that the price lists are on different rows. If they are on the same row (price1, price2 . . .), then this will work:

 select pi.ItemCode, pi.ItemName,
        coalesce(pl.price6, pl.price1*0.95) as price
 from ProductInformation pi left outer join
      PriceList pl
      on pi.ItemCode = pl.ItemCode;

score 0 · Answer 2 · answered Jun 19 '13 at 10:58

0

You need the coalesce function which looks like:

SELECT COALESCE(T1.PRICE,"MISSING") FROM [TABLE]

You can find more information on it in your RDBMS' documentation but this will return "missing" if the field returns a NULL.

answered Jun 19 '13 at 10:58

Steph Locke

5,951
4
39
77

Returning another value if preferred value is null

2 Answers2