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

Question

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 ?

Midhun T P · Answer

Hi Please try below query - With Family As ( Select s.IDs.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.IDs2.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

Nilesh Patil · Answer

Hi Refer below links and implement it in your solution https://www.w3resource.com/sql/joins/perform-a-self-join.php

basim salih · Answer

Dear Sir Midhun T P thank you for ur solution ... I wish you success happiness and more science With Best regards

basim salih · Answer

thank you very much my friend Tushar Dikshit

Tushar Dikshit · Answer

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 salih · Answer

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

Midhun T P · Answer

Hi Please try below query - SELECT f1.idf1.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