Can anyone solve the SQL puzzle ?

• Sandeep-248595

  SSC Enthusiast

  Points: 196

  More actions

  March 5, 2007 at 11:18 pm

  #369715

  A puzzle for SQL Masters !!!

   

  Table1
  id	Name
  1	X
  2	Y
  3	Z
  4	A
  5	C
  6	x

  Table2
  id	Name
  1	A
  2	B
  4	B

  Table3
  id	Name
  1	P
  5	C

   

  Take Id as integer and Name as varchar in all three tables Result should be as below   id Name 1 P 2 B 3 Z 4 B 5 C 6 x   Good luck!! Regards, Sandeep

• twilson111

  SSC Veteran

  Points: 290

  More actions

  March 6, 2007 at 12:06 am

  #693256

  Here's my quick and dirt solution, excluding the table creation and inserts code...

   

  select

  t1.id,

  Name = case

  when t3.id is not null then t3.[Name]

  when t2.id is not null then t2.[Name]

  else t1.[Name] end

  from Table1 t1

  left join Table2 t2 on t1.id = t2.id

  left join Table3 t3 on t1.id = t3.id

   

   

  
  Td Wilson

• AMIT GUPTA-263376

  SSCarpal Tunnel

  Points: 4244

  More actions

  March 6, 2007 at 3:39 am

  #693266

  Hi ,

  Excellent Question and very good answer by Td Wilson ...

  Good One Boss..............

   

  Regards ,

  Amit Gupta...

   

   

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  March 6, 2007 at 3:45 am

  #693267

  Same result may be achieved with a little bit simpler query:

  select t1.id, COALESCE (t3.[Name], t2.[Name], t1.[Name]) as Name

  from Table1 t1

  left join Table2 t2 on t1.id = t2.id

  left join Table3 t3 on t1.id = t3.id

  _____________
  Code for TallyGenerator