Can help me to perform sql query in self join table?

basim salih

8y1.4kSQL

Let us have a table (Family Tree) table as ( a self join query )

How can I perform a queries to find all Grandsons for : 1- James. 2- Sami ?

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Midhun Tp8y ago
Accepted answer
Hi,

Please try below query -

With Family As
(
Select s.ID,s.name, s.father_id, 0 as Depth
From family_tree s
Where ID = 3 -- this was removed if you intend to get all hierarchy of the record.
Union All
Select s2.ID,s2.name, s2.father_id, Depth + 1
From family_tree s2
Join Family
On Family.ID = s2.father_id
)
Select *
From Family

Reference :
https://stackoverflow.com/questions/3471134/how-to-self-join-recursively-in-sql
basim salih8y ago
Dear Sir
Midhun T P
thank you for ur solution ...
I wish you success, happiness and more science
With Best regards
basim salih8y ago
thank you very much my friend Tushar Dikshit
Tushar Dikshit8y ago
hi,

Please see following query, it will give you tabular details.
select f1.id, f1.name, f1.father_id, f2.name as fathername, f3.name as grandfathername
from familyTree f1
left join familyTree f2
on f1.father_id=f2.id
left join familytree f3
on f2.father_id= f3.id
Created table with same input as given in question.

Second result is just all table to verify.
For grandsons of James and Sami
select f3.name as grandfathername ,f2.name as fathername, f1.name,
f1.id, f1.father_id
from familyTree f1
left join familyTree f2
on f1.father_id=f2.id
inner join familytree f3
on f2.father_id= f3.id
and f3.name in ('james','sami')
Thanks
.
basim salih8y ago
Sir Midhun....
thank you very much for your solution ...
ur solution show only (sami, joseph, isam , kamil)... that's not all what i want to view in query.
I want to show in query all sons for james. for example.
foor all sons for Sami that them number is 4 sons (sons and progeny )
for exmple : (sami ) has (sons : Sohad , jacoub and progeny him: Solal and Smith)
in query must view all these names for (sami), and same thing for (James)

With best regard
Nilesh Patil8y ago
Hi,

Refer below links and implement it in your solution

https://www.w3resource.com/sql/joins/perform-a-self-join.php
Midhun Tp8y ago
Hi,

Please try below query -

SELECT f1.id,f1.name,
f3.name AS grandson FROM Family_Tree f1 INNER JOIN Family_Tree f2 ON f1.id=f2.father_id
INNER JOIN Family_Tree f3 ON f2.id=f3.father_id

Can help me to perform sql query in self join table?

Midhun T P