CODEWITHSUNDEEP
sql
user2500775
user2500775

Reputation: 1

Returning another value if preferred value is null

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

Upvotes: 0

Views: 193

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269893

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;

Upvotes: 1

Steph Locke
Steph Locke

Reputation: 6146

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.

Upvotes: 0

Related Questions