locked
can someone help me on this query? RRS feed

  • Question

  • CREATE TABLE T1
    (
    col1 int
    )
    CREATE TABLE T2
    (
    col1 int
    )
    CREATE TABLE T3
    (
    col1 int,
    col2 int NULL,
    col3 CHAR(1)
    )

    insert into T1
    select 1
    insert into T1
    select 2
    insert into T1
    select 3

    insert into T2
    select 100

    insert into T3
    select 100,1,'X'
    insert into T3
    select 100,NULL,'Y'

    I am trying to achieve the result as
    T1.col1, T3.col3

    1 X
    2 Y
    3 Y

    so I written this query
    SELECT T1.col1,T3.col3
    FROM T1
    JOIN T2 ON 1=1
    LEFT JOIN T3 ON (T3.col1 = T2.col1 AND (T1.col1 = T3.col2 OR T3.col2 IS NULL) )

    give me 4 records

    please help.
    Friday, February 19, 2010 2:43 PM

Answers

  • use tempdb
    CREATE TABLE T1 
    (
    col1 int
    )
    CREATE TABLE T2
    (
    col1 int
    )
    CREATE TABLE T3
    (
    col1 int,
    col2 int NULL,
    col3 CHAR(1)
    )
    
    insert into T1
    select 1
    insert into T1
    select 2
    insert into T1
    select 3
    
    insert into T2
    select 100
    
    insert into T3
    select 100,1,'X'
    insert into T3
    select 100,NULL,'Y'
    
    
    select * from T1
    select * from T2
    select * from T3
    
    
    
    
    ;with  cte1 as 
    (select t1.col1,t3.col3 from T1 t1 left join T3 t3 on t1.col1=t3.col2
    )
    
    , cte2 as
    (select col3 as xyz  from T3 where col2 is null
    )
    
    
    
    select col1,case when col3 is NULL then xyz else col3 end from  cte1,cte2

    Busy in learning!!
    • Marked as answer by Kalman Toth Wednesday, February 24, 2010 9:12 PM
    Friday, February 19, 2010 3:01 PM
  • Here is one solution:

    SELECT T1.col1, COALESCE(T3.col3, T3_b.col3) AS col3
    FROM T1
    LEFT OUTER JOIN T3
      ON T1.col1 = T3.col2
    LEFT OUTER JOIN T2
      ON T3.col1 = T2.col1
    LEFT OUTER JOIN T3 AS T3_b
      ON T3_b.col2 IS NULL;

    Plamen Ratchev
    • Proposed as answer by Naomi N Friday, February 19, 2010 4:40 PM
    • Marked as answer by Kalman Toth Wednesday, February 24, 2010 9:11 PM
    Friday, February 19, 2010 4:34 PM

All replies

  • use tempdb
    CREATE TABLE T1 
    (
    col1 int
    )
    CREATE TABLE T2
    (
    col1 int
    )
    CREATE TABLE T3
    (
    col1 int,
    col2 int NULL,
    col3 CHAR(1)
    )
    
    insert into T1
    select 1
    insert into T1
    select 2
    insert into T1
    select 3
    
    insert into T2
    select 100
    
    insert into T3
    select 100,1,'X'
    insert into T3
    select 100,NULL,'Y'
    
    
    select * from T1
    select * from T2
    select * from T3
    
    
    
    
    ;with  cte1 as 
    (select t1.col1,t3.col3 from T1 t1 left join T3 t3 on t1.col1=t3.col2
    )
    
    , cte2 as
    (select col3 as xyz  from T3 where col2 is null
    )
    
    
    
    select col1,case when col3 is NULL then xyz else col3 end from  cte1,cte2

    Busy in learning!!
    • Marked as answer by Kalman Toth Wednesday, February 24, 2010 9:12 PM
    Friday, February 19, 2010 3:01 PM
  • One more way

    Select Col1,
    Case
    When Col3 is not null then Col3
    else 'Y'
    end as Col3 from 
    (Select #T1.Col1,#T3.Col2,#t3.Col3 from #T1
    left join #T3
    on 
    #T3.Col2=#T1.Col1) as a
    Thanks
    Friday, February 19, 2010 3:20 PM
  • Here is one solution:

    SELECT T1.col1, COALESCE(T3.col3, T3_b.col3) AS col3
    FROM T1
    LEFT OUTER JOIN T3
      ON T1.col1 = T3.col2
    LEFT OUTER JOIN T2
      ON T3.col1 = T2.col1
    LEFT OUTER JOIN T3 AS T3_b
      ON T3_b.col2 IS NULL;

    Plamen Ratchev
    • Proposed as answer by Naomi N Friday, February 19, 2010 4:40 PM
    • Marked as answer by Kalman Toth Wednesday, February 24, 2010 9:11 PM
    Friday, February 19, 2010 4:34 PM