title: COALESCE function won't return CHAR(1)
body: <p>Using <code>COALESCE</code> function but getting the following error:</p>
  
  <blockquote>
    <p>Conversion failed when converting the varchar value 'X' to data type int.</p>
  </blockquote>
  
  <p>I have to join two tables on two conditions. I want that if the second condition doesn't hold but there is a blank cell (not null but blank '') in Table 1 then to join to that row. If the second condition doesn't hold then to return a zero. </p>
  
  <p>Join Table 1 and Table 2 - return Table 2 and column 3 from Table 1.</p>
  
  <p>Table 1</p>
  
  <pre><code>(A, 1, X),
  (A, 2, Y),
  (A, 3, Z),
  (A, , X),
  (B, 1, X),
  (B, 2, Z),
  (B, 3, Y),
  </code></pre>
  
  <p>Table 2 </p>
  
  <pre><code>(A, 1),
  (A, 2),
  (A, 3),
  (A, 5),
  (B, 1),
  (B, 2),
  (B, 3),
  (B, 5)
  </code></pre>
  
  <p>I want to get a return of </p>
  
  <pre><code>(A, 1, X),
  (A, 2, Y),
  (A, 3, Z),
  (A, 5, X),
  (B, 1, X),
  (B, 2, Z),
  (B, 3, Y),
  (B, 5, NULL)
  </code></pre>
  
  <p>Code:</p>
  
  <pre><code>DECLARE @table1 TABLE (letter1 CHAR(1), num1 INT, letter2 CHAR(1))  
  DECLARE @table2 TABLE (letter1 CHAR(1), num1 INT)  
  
  INSERT INTO @table1 VALUES    
  ('A', 1, 'X'),  
  ('A', 2, 'Y'),  
  ('A', 3, 'Z'),  
  ('A', null, 'X'),  
  ('B', 1, 'X'),  
  ('B', 2, 'Y'),  
  ('B', 3, 'Z')  
  
  INSERT INTO @table2 VALUES 
  ('A', 1),  
  ('A', 2),  
  ('A', 3),  
  ('A', 5),  
  ('B', 1),  
  ('B', 2),    
  ('B', 3),  
  ('B', 5) 
  
  SELECT t2.*,   
      COALESCE(  
          (SELECT TOP 1 letter2 FROM @table1 WHERE letter1 = t2.letter1 AND num1 = t2.num1),   
          (SELECT TOP 1 letter2 FROM @table1 WHERE letter1 = t2.letter1 AND num1 IS NULL),   
          0  
      ) AS missing_letter  
  FROM @table2 t2  
  </code></pre>