title: SQL Left Outer Join Conditionally
body: <p>I'm running a very conditional query in MS SQL. I've inherited this database and it's structure can't be changed. Due to poor design the one field (PlantName) can either house a string or a int that is used to reference a string in another table(SoilSpecies). I'm trying to keep this all down in one query, and I'm trying to bring the SoilSpecies table in on a Left Outer Join. The problem I can't compare the PlantName strings in many of the rows to the Int primary key on SoilSpecies. </p>
  
  <p>And Example would be: </p>
  
  <p>"Conversion failed when converting the nvarchar value 'rose' to data type int."</p>
  
  <p>I was wondering if it was possible conditionally set the contents of a field to '0' if it's not already on INT for the purposes of a join. </p>
  
  <pre><code>select distinct sv.sampleno, sv.plantName,
  sv.tests, sv.plantNameOther, sv.originalsubmitContact, 
  sv.sampletype, sv.test_foliar, sv.form, 
  COALESCE (ci.clname, BTE_Customer.name) AS clname,
  o.officename,  
  
  CAST(di.id as BIT) as DIAGIMAGES,
  
  sv.PlantLocation,
  CASE 
  WHEN (sv.form = 'soils') THEN sv.plantName
  ELSE NULL
  END
  AS sampleType,
  
  
  CASE
  WHEN (sv.form = 'soils') THEN ss.name
  ELSE NULL
  END
  AS ssName
  
  from searchview sv  
  left outer join clientinfo  ci on 
      sv.clientid = ci.clientid 
  left outer join  BTE_Customer on 
      BTE_Customer.BTE_ID = sv.BTE_ID
  left outer join  BTE_CustomerProperty on 
      sv.BTE_ID = BTE_CustomerProperty.BTE_ID AND sv.Property_ID = BTE_CustomerProperty.Property_ID
  left outer join offices o on
      sv.officeID = o.officeID
  left outer join reply r on 
      sv.sampleno = r.sampleno
  left outer join diagnosticimages di on 
      sv.sampleno = di.sampleno
  left outer join SoilsSpecies ss on
      (sv.plantname = ss.name) or (sv.plantname = ss.SpeciesID) 
  
  
  
  WHERE (not sv.sampleno = 0)
  
  and form in ('Soils','Plants')
  
  
  
  
  AND (ci.clname like '%NAME%' or BTE_Customer.name like '%NAME%')
  </code></pre>