title: How to properly construct SQL subquery in this code?
body: <p>When I execute the following code, I'm getting results such as:</p>
  
  <pre><code>ID  column1 column2 
  
  34  NULL    NULL
  34  Org13   Org13
  36  NULL    NULL
  36  NULL    Org2
  36  Org4    NULL
  41  NULL    NULL
  41  NULL    Org5
  41  Org3    NULL
  </code></pre>
  
  <p>I want my results to look like:</p>
  
  <pre><code>ID  column1  column2
  
  34  Org13   Org13
  36  Org4    Org2
  41  Org3    Org5
  </code></pre>
  
  <p>I've got two tables:  Table1 and Table2.  Table2 is a lookup table with the following fields:    id, name</p>
  
  <p>Table1 has the following fields (id, column1, column2).  column1 and column2 both have foreign key relationships to the lookup table:</p>
  
  <pre><code>FK_1: Table1.column1-Table2.id
  FK_2: Table1.column2-Table2.id
  </code></pre>
  
  <p>Since I want to pull out the values for column1 and column2, and since both of these values are lookups on the same field (Table2.name), I suspect I need to do inner Selects.</p>
  
  <p>My code is below.  How can I change this so that it produces the results desired, instead of the ones I'm getting?  Thanks in advance!</p>
  
  <pre><code>DECLARE @value INT
  SET @value = 14
  
  SELECT DISTINCT 
      Table1.[id]         AS ID
      , ( SELECT DISTINCT
              Table2.[name] 
          WHERE 
              Table1.column1 =
              Table2.id ) AS column1
      , ( SELECT DISTINCT
              Table2.[name] 
          WHERE 
              Table1.column2 =
              Table2.id ) AS column2
  FROM 
      Table1
      ,Table2
  WHERE   
      Table1.[id] = @value
  </code></pre>