joining 2 tables that have 1 to any relationship and applying some conditions in sql server 2008
Posted on 2011-10-12
I have 2 tables. One is:Item
ItemID Itemnmbr Price
Another table ItemException
ItemID StoreID PriceException
For each ItemID in Item table there could be no record in ItemPriceException or 1 record or multiple records in ItemPriceException.
Now what I want to do is
1) select all from item where there are no corresponding rows in ItemPriceexception
2) if there is a row in ItemPriceexception with its corresponding store ID say 500, then return this record with price from ItemPriceexception
3) If there is a row in ItemPriceexception with storeid = 0, then this will supersede any other ItemPriceexception or the default price in the Item table.
What is the most efficient way to do this. Can I do this using 1 query, or may be get results in temp tables and then do Union?
Please help. Thanks.