title: Joining on derived column without using subquery
body: <p>In MySQL I'm trying to join two tables on a derived column, something like this:</p>
  
  <pre><code>SELECT C.country, 
    IFNULL(C.preferred_city, C.default_city) AS best_city,
    PC.postal_code
  FROM Countries as C
  INNER JOIN PostalCodes AS PC
  ON best_city=PC.city
  </code></pre>
  
  <p>This of course does not work because <code>best_city</code> is outside the scope of the <code>ON</code> clause.</p>
  
  <p>The only solution I could find was using a subquery (or is this considered a derived table? I'm fuzzy on the nomenclature):</p>
  
  <pre><code>SELECT BC.*, PC.postal_code
  FROM (
    SELECT country, IFNULL(preferred_city, default_city) AS best_city
    FROM COUNTRIES
  ) AS BC
  INNER JOIN PostalCodes AS PC
  ON BC.best_city=PC.city
  </code></pre>
  
  <p>which works nicely from the shell.  However, I'm trying to implement this as a view and it gives me the error:  <em>ERROR 1349: View's SELECT contains a subquery in the FROM clause</em></p>
  
  <p>How can I rework this to join without using a subquery??</p>
  
  <p>Thanks!</p>