title: Using Where clause in Case to compare
body: <p>I am new to T-sql .</p>
  
  <p>I am using the following Query:</p>
  
  <pre><code>SELECT e.Id,e.cAvg,
  CASE 
      WHEN e.cAvg&lt;=0.8 and cAvg&gt;=0 THEN t.Model when t.Cr='0.8' then t.Model 
      WHEN e.cAvg&gt;0.8 and cAvg&lt;=5.4 THEN t.Model WHEN t.Cr='5.4' then t.Model 
      WHEN e.cAvg&gt;5.4 and cg&lt;=8 THEN t.Model WHEN t.Cr='8' then t.Model 
      ELSE 'No Change Required'
    END
    from A e, B t;
  </code></pre>
  
  <p>What I am trying to do is:</p>
  
  <ul>
  <li>Select id and cAvg columns in Table A.</li>
  <li>Compare cAvg in Table A with Cr in Table B.</li>
  <li>Use the comparison in CASE to select the particular row which satisfies the condition.</li>
  <li>Use the selected row to give query results.</li>
  </ul>
  
  <p>t.Model is a column of table B. I want to select t.Model value of the selected row in the case statement.</p>
  
  <p>I feel the way is to somehow include a equivalent of the where clause inside When of CASE.</p>
  
  <p>Need Direction!!</p>
  
  <p>The table schema:</p>
  
  <p>Table A:</p>
  
  <pre><code>+----+------+
  | id | cAvg |
  +----+------+
  |  1 | .8   |
  |  2 | 5.4  |
  |  3 | 6.0  |
  +----+------+
  </code></pre>
  
  <p>Table B:</p>
  
  <pre><code>+-----+-------+
  | Cr  | Model |
  +-----+-------+
  | 2   | M1    |
  | 5.5 | M2    |
  | 8   | M3    |
  +-----+-------+
  </code></pre>
  
  <p>I want to the following:</p>
  
  <ul>
  <li>Compare the values of cAvg with a condition => (cAvg&lt;=8 And cAvg>=5.5 => the model selected must be M3.)</li>
  </ul>
  
  <p>The result I want to get is:</p>
  
  <pre><code>+----+------+-------+
  | id | cAvg | Model |
  +----+------+-------+
  |  1 | .8   | M1    |
  |  2 | 5.4  | M2    |
  |  3 | 6.0  | M3    |
  +----+------+-------+
  </code></pre>
  
  <p>I tired Join as suggested in the comments, A great thanks , I learnt a lot because of it!!.</p>
  
  <p>My problem is that there are no common columns to join.
  Also I need to compare the column in one table with that of another table and then give a result based on the comparison.</p>
  
  <p>I referred to many answers in stack overflow but all the answers are for the premise where there is a common column.</p>
  
  <p>I tried the following:</p>
  
  <ul>
  <li>Inner Join</li>
  <li>Cases</li>
  </ul>
  
  <p>I need a direction as to which direction I need to go into.
  Thank you!!</p>