Is it possible thru Self-Join??

• Venkata-433090

  Old Hand

  Points: 336

  More actions

  May 5, 2007 at 4:27 pm

  #178274

  Folks,

  I have this issue here:

  Table: Part (pno int, pname varchar(25), old_pno int)

  A part can be renamed to another part and the new part will be created as a new record.  The old part number will be populated into old_pno of the new part record.  The data in this table will be like:

   

  pno         pname                  old_pno    

  ----------- ---------------------- -----------

  1           Andhra01               NULL

  2           Andhra02               1

  3           Andhra02               2

  4           NULL                   NULL

  Now, I want to find out the chain of parts for part number 3, (ie) I need to get parts with pno: 1, 2 and 3.  Please suggest how to write the query. 

   

  thanks in advance.

  M.

   

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736478

  More actions

  May 5, 2007 at 5:34 pm

  #704521

  You want to walk a hierarchy.

  http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp