Quering Nested Data in a table

2489128

Hi. I've a table Project(ProjectId, ParentProjectId, ProjectName) the sample data are -------------------------- 1,0, X 2,1,Y 3,2,Z 4,3,W now for a given project id i want to get projectid, projectname for a given projectid. e.g. i want 1,X for projectid = 4 i want to SQL query for this. plz help me.

Mycroft Holmes

So what have you tried? Anyone who has read more than 1 chapter of a book on SQL or has spent more than 10 minutes learning what to do can answer this. It is so basic that I suspect you tobe a troll.

Never underestimate the power of human stupidity RAH

David Skelly

To be fair, it's not quite that straightforward if you look at the data.

ProjectId ParentProject ProjectName
1 0 X
2 1 Y
3 2 Z
4 3 W

What he wants (I think) is to start from project 4, and go all the way back to the root parent. So, 4 is a child of 3 which is a child of 2 which is a child of 1 which has no parent. So, he wants to know that project 4 (name W) is part of the hierarchy ultimately descending from project 1 (name X). The SQL to pick out the ultimate root parent when you don't know how deep the hierarchy might be is actually quite challenging. I'm not sure a beginner could write this. I'm not sure I could, to be honest.

Niladri_Biswas

Hi, First of all the question is unclear. What I made out is that, you want to get the parent record of 4 i.e. 1 What if I pass the Projectid = 3 or 2? You should give the relevant scenarios so that others can also make out. Any way, based on what I understand, here is the solution

SELECT PROJECTID,PROJECTNAME

FROM PROJECT

WHERE PROJECTID = ( SELECT P1.PARENTPROJECTID + 1
FROM PROJECT P1
INNER JOIN PROJECT P2
ON P2.PROJECTID - 4 = P1.PARENTPROJECTID )

Hope this helps. But as what I found that if for 4 the parent is 1, the same applies to 2 or 3 also. In that case why not you are making a top count? Let me know in case of any concern :)

Niladri Biswas

Henry Minute

Take a look at this[^] article. If it does not actually solve your problem, it should at least give you some terms to Google.

Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”

Mycroft Holmes

I'm impressed, I did not get that from the question at all. And yes building the hierarchy stuff is quite challenging. I did an article on using it in C# but the SQL stuff I left out for this reason. It is also not well covered.

Never underestimate the power of human stupidity RAH

hemant kaushal

Select @parentid=parentid from tablename where projectid=4

if(@parentid<>0)
Begin
WHILE (@PARENTID <> 0 )
BEGIN

Select @parentid=parentid from tablename where projectid=@parentid

if(@parentid is null)
break
set @id=@parentid
End
End

You can use these logic to get the parentid, Variable @id will contain your final parentid I had not tested this code so u may have to made some change in this. It may get in infinite loop so i used

if(@parentid is null)
break