title: SQl calculate column value based on reference table
body: <p>I want to select  a column value based on the reference table in SQL Server.</p>
  <p>These are the tables:</p>
  <p><strong>Tbl1</strong></p>
  <pre><code>id   Cost   LineNo 
  -----------------------
  1     10      01
  2     20      02
  3     30      03
  </code></pre>
  <p><strong>Tbl2</strong></p>
  <pre><code>id  LineNo MaterialCost
  --- -----  -----------
  1    03     50
  </code></pre>
  <p>I want to calculate <code>Totalcost</code> (as <code>line no * Materialcost</code>) from <code>tbl2</code> if there is a matching lineno in <code>tbl2</code> from <code>tbl1</code> else get the TotalCost (<code>lineno * Cost</code>) from tbl1</p>
  <p>Below is the output I want</p>
  <pre><code>id   Cost   LineNo          TotalCost
  -----------------------     ---------
  1     10      01             10
  2     20      02             40
  3     30      03            150
  </code></pre>