title: Select column if not NULL then select other
body: <p>I'm trying to figure how to return a row with the associated value when using the JOIN syntax, firstly it should check the <code>links</code> table to see if the row is <code>NULL</code>, if it's not <code>NULL</code> use that for the <code>JOIN</code> e.g. <code>ON links.role_id = roles.role_id</code> if the row is <code>NULl</code>. If it's <code>NULL</code> then use the <code>people</code> table.</p>
  
  <p>My syntax:</p>
  
  <pre><code>SELECT roles.role_name
  FROM
  (
      people_links links
  
      LEFT OUTER JOIN people people ON links.person_id = people.person_id
  
      LEFT OUTER JOIN roles roles ON links.role_id = roles.role_id OR links.role_id = people.role_id
  )
  </code></pre>
  
  <p>Basically when joining and using the <code>ON</code> syntax with the <code>role_id</code>, it will return the associated value from the <code>roles</code> table that is used from the <code>role_id</code> so it should rely on the <code>links</code> table <strong>first</strong>, check if <code>links.role_id</code> is <code>NULL</code> then return <code>people.role_id</code> else <code>links.role_id</code> for the <code>JOIN</code> to get the <code>role_name</code>.</p>
  
  <p>When I run this query, it uses the <code>role_id</code> from the <code>people</code> table first, when it should look in the <code>links</code> table first.</p>
  
  <p>I tried using <code>COALESCE</code> but that will only return the <code>role_id</code> when it should return <code>role_name</code>, so for example (pseudo code):</p>
  
  <pre><code>SELECT IF(links.role_id IS NOT NULL) links.role_id ELSE people.role_id ENDIF as join_value
  FROM
  (
      people_links links
  
      LEFT OUTER JOIN people people ON links.person_id = people.person_id
  
      LEFT OUTER JOIN roles roles ON roles.role_id = join_value
  )
  </code></pre>