Reputation: 17904
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
Upvotes: 0
Views: 389
Reputation: 2449
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
Upvotes: 2
Reputation: 475
/*
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]
Upvotes: 3
Reputation: 432672
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]
Upvotes: 2