title: How to get a single row result for data
body: <p>I have the following two tables,</p>
  <p>Table <em>Periods</em>,</p>
  <pre><code>Id  Names
  1   Simon
  2   Davis
  </code></pre>
  <p>Table <em>Tier</em>,</p>
  <pre><code>Id    sYear        days
  1      0             0      
  1      1             21     
  1      5             30 
  2      0             0      
  2      1             26     
  2      5             30      
  </code></pre>
  <p>The table schema is not the best, but not much i can do about it.</p>
  <p>I need to retrive the days for an id based on the following criteria,</p>
  <ul>
  <li>If sYear is 0 return 0</li>
  <li>If sYear is between 1 and 5 return 21, or 26 if it is id 2 in the above sample data</li>
  <li>if sYear is greater than 5 return 30</li>
  </ul>
  <p>The <em><strong>days</strong></em> column values differ for the different id's but <em>sYear</em> is always 0,1 &amp; 5.</p>
  <p>My attempt looks like,</p>
  <pre><code>set @sYear = 7;
  
  SELECT 
      et.id,
      names,
      CASE
          WHEN @sYear = 0 THEN days
          WHEN @sYear BETWEEN 1 AND 5 THEN days
          WHEN @sYear &gt; 5 THEN days
      END as Result
  FROM
      Periods ep
          JOIN
      Tier et ON (et.id = ep.id) AND et.id = 2;
  </code></pre>
  <p>which gives the results as,</p>
  <pre><code>id  names   Result
  2   Davis    0
  2   Davis    26
  2   Davis    30
  </code></pre>
  <p>whereas the result required is,</p>
  <pre><code>id  names   Result
  2   Davis   30
  </code></pre>
  <p>as <em>@sYear = 7</em> is greater than 5.</p>
  <p><a href="https://dbfiddle.uk/?rdbms=mysql_8.0&amp;fiddle=13f84d4a34d426a28ea08b7a9df35d01" rel="nofollow noreferrer">dbfiddle</a></p>