title: Should I use CASE WHEN or IIF? Are subqueries a possible part of the solution?
body: <p>I'm using My SQL (latest version) and I have the following table:</p>
  <pre><code>CREATE TABLE COURSES (
  IDCOURSE INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (30) NOT NULL, 
  HOURS INT,
  PRICE FLOAT (10,2) NOT NULL, 
  ID_PREREQ INT
  );
  
  ALTER TABLE COURSES ADD CONSTRAINT FK_PREREQ
  FOREIGN KEY (ID_PREREQ) REFERENCES COURSES (IDCOURSE);
  
  </code></pre>
  <p>What I want do is: select the columns NAME, HOURS, PRICE and ID_PREREQ AS REQUISITES.
  Whenever ID_PREREQ is NULL, I want to read &quot;NO REQUISITES&quot;. If else, then I want to read whatever's in the associated NAME column.</p>
  <p>For example:</p>
  <p>If this is my table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">IDCOURSE</th>
  <th style="text-align: center;">NAME</th>
  <th style="text-align: center;">HOURS</th>
  <th style="text-align: center;">PRICE</th>
  <th style="text-align: center;">ID_PREREQ</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">1</td>
  <td style="text-align: center;">RELATIONAL DB</td>
  <td style="text-align: center;">20</td>
  <td style="text-align: center;">400.00</td>
  <td style="text-align: center;">NULL</td>
  </tr>
  <tr>
  <td style="text-align: left;">2</td>
  <td style="text-align: center;">BUSINESS INTELLIGENCE</td>
  <td style="text-align: center;">40</td>
  <td style="text-align: center;">800.00</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">3</td>
  <td style="text-align: center;">ADVANCED DB</td>
  <td style="text-align: center;">20</td>
  <td style="text-align: center;">600.00</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">4</td>
  <td style="text-align: center;">PROGRAMMING</td>
  <td style="text-align: center;">20</td>
  <td style="text-align: center;">400.00</td>
  <td style="text-align: center;">NULL</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I'd like a select to show me this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">NAME</th>
  <th style="text-align: center;">HOURS</th>
  <th style="text-align: center;">PRICE</th>
  <th style="text-align: center;">REQUISITES</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">RELATIONAL DB</td>
  <td style="text-align: center;">20</td>
  <td style="text-align: center;">400.00</td>
  <td style="text-align: center;">NO REQUISITES</td>
  </tr>
  <tr>
  <td style="text-align: center;">BUSINESS INTELLIGENCE</td>
  <td style="text-align: center;">40</td>
  <td style="text-align: center;">800.00</td>
  <td style="text-align: center;">RELATIONAL DB</td>
  </tr>
  <tr>
  <td style="text-align: center;">ADVANCED DB</td>
  <td style="text-align: center;">20</td>
  <td style="text-align: center;">600.00</td>
  <td style="text-align: center;">BUSINESS INTELLIGENCE</td>
  </tr>
  <tr>
  <td style="text-align: center;">PROGRAMMING</td>
  <td style="text-align: center;">20</td>
  <td style="text-align: center;">400.00</td>
  <td style="text-align: center;">NO REQUISITES</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What I've tried so far:</p>
  <p><a href="https://i.stack.imgur.com/eEI5d.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eEI5d.png" alt="enter image description here" /></a></p>
  <p>With this solution, I came across two different problems:</p>
  <p><strong>1. How to show the NAME related to the correct ID_PREREQ?</strong></p>
  <p>I managed to write the following code:</p>
  <pre><code>SELECT CASE WHEN ID_PREREQ IS NOT NULL
      THEN NAME
      ELSE 'NO REQUISITES'
  END
  FROM COURSES;
  </code></pre>
  <p>But as you can see, I'm merely repeting the name column in the REQUISITES column, instead of getting the associated value I want. I was also unable to give the result column its proper name (REQUISITES).</p>
  <p><strong>2. How to select more columns besides the REQUISITES column?</strong></p>
  <p>I've tried this:</p>
  <p><a href="https://i.stack.imgur.com/UyFVJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UyFVJ.png" alt="enter image description here" /></a></p>
  <p>But for some reason I got a syntax error.</p>
  <p>Finally, I also tried the IIF Statement because the syntax seemed easier, but I got an ERROR 1305: FUNCTION does not exist.</p>
  <p>I don't understand why is that, considering that I'm using the latest MySQL version and apparently that IIF statement was released in 2012.</p>
  <p>Can somebody please help me?</p>
  <p>Thank you!</p>