title: check different prices in different tables
body: <p>let's say i have 3 tables:<br/></p>
  
  <ol>
  <li>ITEMS table (all the info about item)</li>
  <li>ITEM_Pricing (multiple prices for the same item)</li>
  <li>Special Prices (IF we have special price for certain items or promotions)</li>
  </ol>
  
  <p>I want to build a query to check for the price if exists in <strong>special prices</strong> table display it, if not take what's in <strong>Item_Pricing</strong> table. in other words i want to check if the item exists in special prices table first with price if not go and check in item_pricing table.
  <br/>this is what i tried, of course not giving the correct answer</p>
  
  <pre><code>select T0.itemcode,T0.ItemName,T0.OnHand,T1.price, T2.Price as Special
  from ITEM T0, PRICES T1, S_PRICES T2
  where 
      T0.ItemCode=T1.ItemCode
  and T0.ItemCode=T2.ItemCode
  </code></pre>
  
  <p><br/><br/>
  looks easy but i can't figure out how to do it?? I'm using sql server 2008 R2
  <br>any help will be appreciated
  <br/>Thank you.</p>