Return parent record of child record

Question

Post reply

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

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

1 Gautham NULL

2 Harsha NULL

3 Kamal 1

4 Sai 2

5 Rajesh 2

6 Pradeep 1

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.,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

xyb8 SSC Veteran Points: 263 More actions · Answer 1

xyb8

SSC Veteran

Points: 263

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: 22598 More actions · Answer 2

Try this

create table #Parent



(

PK			int primary key not null,

Descr			varchar(10)	     not null,

Reference_PK	int		     null

)

insert into #Parent

select 1, 'Gautham',	null	union all

select 2, 'Harsha',	null	union all

select 3, 'Kamal',	1	union all

select 4, 'Sai',	2	union all

select 5, 'Rajesh',	2	union 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: 183686 More actions · Answer 3

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



create table #Parent

(

PK			int primary key not null,

Descr			varchar(10)	     not null,

Reference_PK	int		     null

);

insert into #Parent

select 1, 'Gautham',	null	union all

select 2, 'Harsha',	null	union all

select 3, 'Kamal',	1	union all

select 4, 'Sai',	2	union all

select 5, 'Rajesh',	2	union 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: 1000539 More actions · Answer 4

gautham.gn (5/21/2014)

I need a help regarding this...

There is a table like this

PK Desc Reference_PK

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

1 Gautham NULL

2 Harsha NULL

3 Kamal 1

4 Sai 2

5 Rajesh 2

6 Pradeep 1

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)
Intro to Tally Tables and Functions