How to properly construct SQL subquery in this code?

Question

When I execute the following code, I'm getting results such as:

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

I want my results to look like:

ID  column1  column2

34  Org13 Org13
36  Org4 Org2
41  Org3 Org5

I've got two tables: Table1 and Table2. Table2 is a lookup table with the following fields: id, name

Table1 has the following fields (id, column1, column2). column1 and column2 both have foreign key relationships to the lookup table:

FK_1: Table1.column1-Table2.id
FK_2: Table1.column2-Table2.id

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.

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!

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

Answer 1

DECLARE @value INT
SET @value = 14

SELECT
    t1.[id]                 AS ID
    MAX(t2a.name),
    MAX(t2b.name)
FROM 
    Table1 t1
    LEFT JOIN
    Table2 t2a ON t1.column1 = t2a.id
    LEFT JOIN
    Table2 t2b ON t1.column2 = t2b.id
WHERE   
    t1.[id] = @value
GROUP BY    
    t1.[id]

Answer 2

    /*
    create table table1(id int, col1 int, col2 int);
    create table table2(id int, name varchar(10) );

    insert into table2 values(1, 'org 1');
    insert into table2 values(2, 'org 2');
    insert into table2 values(3, 'org 3');
    insert into table2 values(4, 'org 4');

    insert into table1 values(1, 1, 2);
    insert into table1 values(2, 2, 2);
    insert into table1 values(3, 2, 3);
    insert into table1 values(4, 4, 1);
    */

    select
        a.id,
        b.name as column1,
        c.name as column2
    from
         table1 a
    join table2 b on b.id = a.col1
    join table2 c on c.id = a.col2;


 id     column1     column2    
 -----  ----------  ---------- 
 1      org 1       org 2      
 2      org 2       org 2      
 3      org 2       org 3      
 4      org 4       org 1      

 4 record(s) selected [Fetch MetaData: 3/ms] [Fetch Data: 0/ms] 

 [Executed: 7/7/09 4:07:25 PM EDT ] [Execution: 1/ms]

Answer 3

gbn, I think you meant to write

DECLARE @value INT
SET @value = 1

SELECT --??? DISTINCT 
    t1.[id] AS ID, --- missed comma
    table2a.name,
    table2b.name
FROM 
   Table1 t1
     JOIN Table2 table2a ON t1.column1 = table2a.id
     JOIN Table2 table2b ON t1.column2 = table2b.id -- you have t1.column1 oops
WHERE   
    t1.[id] = @value