title: Handling null values with join across multiple tables
body: <p>My mind is exploding right now.. I can't get any of this to work the way I want to! SQL is seriously such a pain in the butt. (/End Rant)</p>
  
  <p>I have three tables that have some common columns to link with. I am trying to retrieve the ID off one table based on the name from the middle table based on the code from the farthest table. (Excuse my vocabulary, I am not skilled with SQL or its' lingo) If the farthest table has a code not found in the middle table, it is to default to a certain value. Then, the first table will return the default for null values. etc.</p>
  
  <p>Example,</p>
  
  <ul>
  <li><code>tblCounty</code> table has an <code>ID</code> and <code>name</code> column. I am to return the <code>ID</code> from <code>tblCounty</code> based on the <code>name</code> column matching the <code>name</code> column of <code>tblCode</code>. </li>
  <li><code>tblCode</code> has two columns <code>name</code> and <code>code</code>. <code>tblCode</code> returns the respective <code>name</code> based on the matching <code>code</code> column with <code>tblAddress</code>'s <code>code</code> column. </li>
  <li><code>tblAdress</code> has many columns, but shares in common a code field.</li>
  </ul>
  
  <p>My attempt,</p>
  
  <pre><code>INSERT INTO vendor (CountyID, Contact)
    SELECT 
       (SELECT a.id 
        FROM county a 
        WHERE a.name = (CASE WHEN (SELECT TOP(1) c.countyID 
                                   FROM tblAdress c 
                                   INNER JOIN tblCode d ON c.CountyID = d.CodeID 
                                   WHERE d.CodeID = b.CountyID) IS NULL THEN '**NONE**' 
                             ELSE (SELECT a.CodeName 
                                   FROM tblCode a 
                                   WHERE a.CodeID = b.CountyID) END)),
       b.Contact
    FROM 
       tblAdress b
  </code></pre>
  
  <p>The error I am receiving is:</p>
  
  <blockquote>
    <p>Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression.</p>
  </blockquote>
  
  <p>Now of course I googled this and looked at results on StackOverflow, but I was unable to apply what I found to my problem.</p>
  
  <p><strong>Vendor</strong>:</p>
  
  <pre><code>CountyID  |  .... 
  -------------------
  1         | ... 
  2         | ... 
  3         | ... 
  2         | ... 
  </code></pre>
  
  <p><strong>tblCounty</strong>:</p>
  
  <pre><code>ID   |   Name  | ...
  ----------------------
  1    | **None** 
  2    |  NYC 
  3    |  Buffalo 
  </code></pre>
  
  <p><strong>tblCode</strong>:</p>
  
  <pre><code> Name     |  Code
   --------------
   **None** |  **None** 
   NYC      |    56A 
   Buffalo  |    75B
  </code></pre>
  
  <p><strong>tblAdress</strong>:</p>
  
  <pre><code>Code     |  ....
  ----------------
  **None** |  .... 
  56A      | ...... 
  75B      | ..... 
  56A      | .....
  </code></pre>
  
  <p>Using the above tables, I want to transfer all data out of <code>tblAdress</code> into another table (<code>vendor</code>). In the process I will convert column <code>Code</code> to <code>tblCode</code>'s column <code>name</code> via <code>code</code> comparison, then to <code>tblCounty.ID</code> via <code>name</code> comparison.</p>
  
  <p><strong>Essentially a catch all is needed.</strong> If a code in <code>tblAddress</code> does not exist in <code>tblCode</code> or the code is null in <code>tblAddress</code>, it will return a default value (<code>None</code>). Then <code>tblCounty</code> will convert that default value into <code>ID = 1</code>, then store it into the <code>Vendor</code> table.</p>
  
  <p><strong>Edit</strong></p>
  
  <pre><code>(SELECT TOP(1)
          c.ID
  FROM 
          dbo.Address a
          LEFT OUTER JOIN 
          dbo.tblCode cd ON ISNULL(CASE a.CountyID WHEN ' ' THEN '**None**' ELSE a.CountyID END, '**None**') = cd.CodeID
          LEFT OUTER JOIN 
          dbo.tblCounty c ON c.NAME = cd.CodeName
  WHERE a.CountyID = b.countyID)
  </code></pre>