title: MySQL - COALESCE using result to join
body: <p>I am trying to get JOIN a table after I get a result from a SELECT with COALESCE</p>
  
  <pre><code>SELECT
      COALESCE(
              (
                  SELECT
                        relocations.location_id
                  FROM
                        relocations
                  WHERE
                        parts.id=relocation.part_id
                  ORDER BY
                        relocations.date_moved
                  DESC
                  LIMIT 
                        1
              )
              ,
              parts.location_id
         ) AS locationId,
      locations.name
  FROM
      parts
  JOIN
      locations ON locations.id=locationId
  </code></pre>
  
  <p>However the query is not valid and MySQL tells me locationId is an Unknown column. How could I fix this?</p>
  
  <p><strong>Table structure</strong></p>
  
  <p>[Parts]
  id | location_id</p>
  
  <p>[Relocations]
  id | location_id | date_moved</p>
  
  <p>[Locations]
  id | name</p>