title: Select value when no relationship from another table
body: <p>I have 2 tables with 1 column that should have a foreign key but unfortunately no FK constraint was applied and the value inserted became inconsistent</p>
  
  <pre><code>table1
  --------
  id    name    consultant
  1    mike         1
  2    allan        2
  3    jenny        0
  4    dan         -1
  5    kevin        patrick
  6    Sarah        
  
  
  consultant
  ----------
  id     name
  1      steve
  2      james
  3      craig
  </code></pre>
  
  <p>i have tried</p>
  
  <pre><code>SELECT
      table1.id AS id,
      table1.name AS name,
      CASE
          WHEN table1.consultant = '0' OR table1.consultant = '' THEN 'For allocation'
          WHEN table1.consultant = '-1' THEN 'Do not allocate'
          WHEN consultant.id NOT EXISTS THEN CONCAT('Cant find:',table1.consultant)
      END AS consultant_name
  FROM
      table1
          LEFT JOIN consultant ON table1.consultant = consultant.id
  </code></pre>
  
  <p>I need to get the result of</p>
  
  <pre><code>id    name     consultant_name
  1     mike     steve
  2     allan    james
  3     jenny    'For allocation'
  4     dan      'Do not allocate'
  5     kevin    'Cant find:patrick'
  6     Sarah    'For allocation'
  </code></pre>