I am new to sqlserver my table having field such as

Its like a tree diagram

4001 is parent so its parent piececode is 4001

4002 is a child for 4001 so its parent piececode is 4001,

4003 is a child for 4002 ,so its parent piececode is 4002,

like that it goes ...no end..

my requirement is i want parent piececode for 4003 output is 4001

output..

ex

4003 parent piececeode is 4002,

4002 parent piececode is 4001,

4001 parent piececode is 4001

<ie>top parent piececode answer: 4001

Your question is not clear. Please rephrase and post again. Also I recommend taking a look at this article: http://dotnetslackers.com/articles/sql/SQL-SERVER-JOINs.aspx