Return parent record of child record

• gautham.gn

  Mr or Mrs. 500

  Points: 590

  More actions

  May 21, 2014 at 3:03 am

  #293480

  I need a help regarding this...

  There is a table like this

  PK Desc Reference_PK

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

  1GauthamNULL

  2HarshaNULL

  3Kamal1

  4Sai2

  5Rajesh2

  6Pradeep1

  I want a select statement to display like...

  1> If Reference_PK column is Null, then it should display desc.

  2> If Reference_PK is having some value ,then it should display the desc of that number.

  please help me with this..

  Thanks.,

• xyb8

  SSC Veteran

  Points: 263

  More actions

  May 21, 2014 at 3:24 am

  #1715255

  SELECT T1.*,ISNULL(T2.Desc,T1.Desc) AS YOUNEED

  FROM [YOUTABLE] T1 LEFT JOIN [YOUTABLE] T2

  ON T1.Reference_PK = T2.PK

• Neil Burton

  SSC-Insane

  Points: 23000

  More actions

  May 21, 2014 at 3:36 am

  #1715257

  Try this

  create table #Parent

  (

  PKint primary key not null,

  Descrvarchar(10) not null,

  Reference_PKint null

  )

  insert into #Parent

  select 1, 'Gautham',nullunion all

  select 2, 'Harsha',nullunion all

  select 3, 'Kamal',1union all

  select 4, 'Sai',2union all

  select 5, 'Rajesh',2union all

  select 6, 'Pradeep',1

  select

  isnull(p.descr,p1.Descr)

  from #Parent p

  right join #Parent p1 on p1.Reference_PK = p.PK

  drop table #Parent

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 21, 2014 at 8:21 am

  #1715381

  If you have more than one level, you could use a recursive CTE.

  create table #Parent

  (

  PKint primary key not null,

  Descrvarchar(10) not null,

  Reference_PKint null

  );

  insert into #Parent

  select 1, 'Gautham',nullunion all

  select 2, 'Harsha',nullunion all

  select 3, 'Kamal',1union all

  select 4, 'Sai',2union all

  select 5, 'Rajesh',2union all

  select 6, 'Pradeep',1 union all

  select 7, 'Someone',5;

  WITH rCTE AS(

  SELECT PK, Descr, Reference_PK

  FROM #Parent

  UNION ALL

  SELECT r.PK, p.Descr, p.Reference_PK

  FROM #Parent p

  JOIN rCTE r ON p.PK = r.Reference_PK

  )

  SELECT PK, Descr

  FROM rCTE

  WHERE Reference_PK IS NULL

  ORDER BY PK;

  GO

  drop table #Parent;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  May 21, 2014 at 5:50 pm

  #1715532

  gautham.gn (5/21/2014)

  ---

  I need a help regarding this...

  There is a table like this

  PK Desc Reference_PK

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

  1GauthamNULL

  2HarshaNULL

  3Kamal1

  4Sai2

  5Rajesh2

  6Pradeep1

  I want a select statement to display like...

  1> If Reference_PK column is Null, then it should display desc.

  2> If Reference_PK is having some value ,then it should display the desc of that number.

  please help me with this..

  Thanks.,

  How many rows do you have in the real table? How often are they updated?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)