Recursive Query using CTE with SQL server2005

• dakshinamurthy-655138

  SSCertifiable

  Points: 5172

  More actions

  April 15, 2009 at 7:16 am

  #136580

  Hi,

  I have 2 tables

  User

  UserId UserName LoginName

  ============================

  1Vijay Teacher001

  2Ningaraju Teacher002

  3Mandir Teacher003

  4Deepak Teacher004

  5Nikethan Teacher005

  UserProfile

  UserId Managername

  ============================

  1NULL

  2Vijay

  3Vijay

  4Mandir

  5Deepak

  In the above 2 tables link to be made is between managername and the loginname

  Please fetch me the details, i tried many queries but couldnt get the output which should be as follows

  LoginName ManagerNAme

  ============================

  VijayNULL

  Ningaraju Vijay

  Mandir Vijay

  Deepak Mandir

  Nikethan Deepak

  With Regards

  Dakshina Murthy

• Abhijit More

  SSCertifiable

  Points: 6897

  More actions

  April 15, 2009 at 7:42 am

  #978071

  i dn't think so you need to user CTE for it it can be done by following way(as mentioned by you only)..

  declare @tbl_User table( UserID INT, Username varchar(10), LoginName varchar(20))

  declare @tbl_UserProfile table(UserID INT, ManagerName varchar(20))

  insert @tbl_User

  select 1, 'Vijay', 'Teacher001' union all

  select 2, 'Ningaraju', 'Teacher002' union all

  select 3, 'Mandir', 'Teacher003' union all

  select 4, 'Deepak', 'Teacher004' union all

  select 5, 'Nikethan', 'Teacher005'

  insert @tbl_UserProfile

  select 1, NULL union all

  select 2, 'Vijay' union all

  select 3, 'Vijay' union all

  select 4, 'Mandir' union all

  select 5, 'Deepak'

  select * from @tbl_User

  select * from @tbl_UserProfile

  select u.Username, up.ManagerName from @tbl_User u join @tbl_UserProfile up on u.UserID = up.UserID

  Abhijit - http://abhijitmore.wordpress.com