title: Why does the sql select statement fail?
body: <p>In the scenario below, the final select from the Combine view fails, any ideas why?  </p>
  
  <p>The Subset table does not have a row that corresponds to the one in MasterCodes that wouldn't cast to an integer value.</p>
  
  <pre><code>CREATE TABLE MasterCodes (
    ID INT
  , Code VARCHAR(10) )
  GO
  
  CREATE TABLE Subset (
    ID INT )
  GO
  
  
  CREATE VIEW Combine AS
  SELECT S.ID
  , M.Code
  , CAST(M.Code AS INT) IntCode
  FROM Subset S
  INNER JOIN MasterCodes M ON M.ID = S.ID
  GO
  
  INSERT MasterCodes (ID, Code) VALUES (1, '1')
  INSERT MasterCodes (ID, Code) VALUES (2, '2')
  INSERT MasterCodes (ID, Code) VALUES (3, 'three')
  INSERT MasterCodes (ID, Code) VALUES (4, '4')
  INSERT Subset (ID) VALUES (1)
  INSERT Subset (ID) VALUES (2)
  INSERT Subset (ID) VALUES (4)
  
  
  SELECT * FROM Combine -- 3 rows returned
  
  SELECT * FROM Combine WHERE Code = '2' -- 1 row returned
  
  SELECT * FROM Combine WHERE Code = '3' -- 0 rows returned
  
  SELECT * FROM Combine WHERE IntCode = 2 -- fails, error msg is
  
  Msg 245, Level 16, State 1, Line 15
  Conversion failed when converting the varchar value 'three' to data type int.
  </code></pre>
  
  <p>Environment is Sql2k5 Standard (64-bit) ON Win2k3 Server R2</p>