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

Daryl · Accepted Answer · 2009-07-07 20:08:05Z

3

    /*
    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]

answered Jul 7, 2009 at 20:08

Daryl

4854 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gbn Over a year ago

what about the NULLs and multiple ID values in table 1?

Daryl Over a year ago

Because he called it his "results", I did not interpret the first output shown in his question as the contents of table1. I assumed the nulls were coming from his incorrect full join in his sample query. Is that incorrect?

Cuga Over a year ago

You're correct in your interpretation-- the nulls were a part of my faulty join-- there are no actual nulls in any of the records.

dplante · Accepted Answer · 2009-07-08 05:19:47Z

2

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

edited Jul 8, 2009 at 5:19

answered Jul 7, 2009 at 20:09

dplante

2,4513 gold badges21 silver badges27 bronze badges

3 Comments

Shannon Severance Over a year ago

If column1 and/or column2 are nullable, you may want to use left joins instead of inner joins so you don't lose rows.

Cuga Over a year ago

Thanks for the heads-up. In this particular case, they're not nullable, but I'll keep your advice in mind =)

gbn Over a year ago

I did have this initially (check history or my post), but then saw the NULLs and changed to the OUTER JOIN with aggregate

gbn · Accepted Answer · 2009-07-07 19:51:19Z

2

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]

answered Jul 7, 2009 at 19:51

gbn

434k84 gold badges602 silver badges690 bronze badges

Collectives™ on Stack Overflow

How to properly construct SQL subquery in this code?

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related