How to fetch Paren Child Rows

• panwar.jt

  Default port

  Points: 1418

  More actions

  January 18, 2011 at 5:57 am

  #230557

  I have one table as below structure.

  create table INF

  (

  InfringmentNo PK,

  ParentInfringementNo FK to InfringmentNo

  )

  Value of table is below.

  InfringmentNo ParentInfringementNo

  1 NULL

  2 1

  3 2

  4 3

  5 NULL

  6 NULL

  7 6

  Now I need to fetch all related record with where condition

  I write the below query

  select infp.InfringementNo,infP.OriginalInfringementNo

  from INF_Infringement inf inner Join INF_Infringement infP ON inf.OriginalInfringementNo=infP.InfringementNo

  output is..

  InfringementNo OriginalInfringementNo

  1 NULL

  2 1

  3 2

  While it should be like

  InfringementNo OriginalInfringementNo

  1 NULL

  2 1

  3 2

  4 3

  I want to specify the Infringment No in Where clause as below

  select infp.InfringementNo,infP.OriginalInfringementNo

  from INF_Infringement inf inner Join INF_Infringement infP ON inf.OriginalInfringementNo=infP.InfringementNo

  where infp.InfringementNo='4'

  Thanks in Advace

  KD

• January 18, 2011 at 6:50 am

  This was removed by the editor as SPAM

• Steve Jones - SSC Editor

  SSC Guru

  Points: 728159

  More actions

  January 18, 2011 at 8:46 am

  #1274784

  This is a hierarchy, and the recursive CTE handles it well. You can read more about it here:

  http://qa.sqlservercentral.com/articles/T-SQL/65540/

  Follow me on Twitter: http://www.twitter.com/way0utwest
  Forum Etiquette: How to post data/code on a forum to get the best help
  My Blog: www.voiceofthedba.com