can someone help me on this query?

All replies

• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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