Sql query to get the GrandFather name by doing a self join

Question

Hello all , Can any one please give me the query to get the child name and his grand fathers name For eg - if i have a table Relationships in the i have childid and fatherid columns so how will i get the grandfather,

i can easily get the father name by using a join but for grandfather i need to do joins 2 times so can any one help me with this

D.Mahesh

Answer 1

Just add an additional join similar to the one you already have.

 select grandparent.name, child.name
 from Relationships child
 inner join Relationships parent
  on child.parentid = parent.id
 inner join Relationships grandparent
  on parent.parentid = grandparent.id

Answer 2

I think its possible with single join as below-

select t2.fatherid as grandfather 
from table1 as t1 
inner join table1 as t2 on t1.fatherid=t2.childid 
where t1.childid='grandson_id';