CODEWITHSUNDEEP
sqlsql-server-2008parent-childhierarchy
Sush
Sush

Reputation: 71

SQL query to retrieve data which is in Parent and child relation in same table. Basically its a hierarchy table

can some one help me with query to get data which is parent - child relation in same table.it is an Hierarchy table

Ex : Table

   Parent_ID | Parent_Name | Child_ID | Child_Name
     1       |    A        |   2      |    B
     2       |    B        |   3      |    C
     3       |    C        |   4      |    Cc
     3       |    C        |   5      |    Ccc
     4       |    Cc       |   6      |    C_C
     1       |    A        |   7      |    D
     7       |    D        |   8      |    Dd
     9       |    E        |   10     |    Ee
     10      |    Ee       |   11     |    Eee

Data Relation of above table is 

            For A -> B is child
                     For  B -> C is child
                           For  C -> Cc and Ccc are Childs
                                     For Cc -> C_C is child

want to retrieve data in following Table format :

    ParentId | Parent_Name | Child_Name | Child_Name | Child_Name | Child_Name
        1    |     A       |     B      |     C      |   Cc       |    Null
        1    |     A       |     B      |     C      |   Ccc      |    Null
        1    |     A       |     B      |     C      |   Cc       |    C_C
        1    |     A       |     D      |     Null   |   Null     |    Null
        1    |     A       |     D      |     Dd     |   Null     |    Null
        9    |     E       |     Ee     |     Null   |   Null     |    Null
        9    |     E       |     Ee     |     Eee    |   Null     |    Null

Another Example

Geography

     Parent_ID | Parent_Name | Child_ID | Child_Name
       1       |    Asia     |   2      |    India
       2       |    India    |   3      |    SouthIndia
       3       |  SouthIndia |   4      |    karnataka
       3       |  SouthIndia |   5      |    Kerela
       4       |  karnataka  |   6      |    Bengaluru
       1       |    Asia     |   7      |    Bangladesh
       7       |  Bangladesh |   8      |    Dhaka
       9       |  Srilanka   |   10     |    Colombo
       10      |  Colombo    |   11     |    Colombo district
       12      |North America|   13     |    Canada
       13      | Canada      |   14     |    Alberta
       13      | Canada      |   15     |    Ontario
       15      | Ontario     |   16     |    Toronto

Expecting as below

Parent_ID|Continent    | Country    | Zone       | State    | City

   1     |   Asia      |  India     | SouthIndia | karnataka| Bengaluru
   1     |   Asia      |  India     | SouthIndia | Kerela   |   
   1     |   Asia      | Bangladesh |            | Dhaka    |   
   1     |   Asia      | Srilanka   |            | Colombo  |Colombo district
  12     |NorthAmerica | Canada     |            | Alberta  |    
  12     |NorthAmerica | Canada     |            | Ontario  | Toronto

Thanks in Advance...

Upvotes: 1

Views: 1424

Answers (1)

Parfait
Parfait

Reputation: 107567

While I do not return your exact desired results as a few of those lines should have been considered in other rows (using posted data), consider the use of self joins all linked by LEFT JOIN.

Additionally, a subquery WHERE clause is needed to filter out original table 1's Parents that may be Children themselves.

Parent-Child Left Joins

SELECT t1.Parent_ID, t1.Parent_Name AS Parent, 
       t1.Child_Name AS Child1, t2.Child_Name AS Child2, 
       t3.Child_Name AS Child3, t4.Child_Name AS Child4    
FROM ParentChild t1 
LEFT JOIN ParentChild AS t2 ON t1.Child_Name = t2.Parent_Name
LEFT JOIN ParentChild AS t3 ON t2.Child_Name = t3.Parent_Name
LEFT JOIN ParentChild AS t4 ON t3.Child_Name = t4.Parent_Name

WHERE (SELECT TOP 1 sub.Parent_Name 
       FROM ParentChild sub 
       WHERE sub.Child_Name = t1.Parent_Name) IS NULL

OUTPUT

Parent_ID   Parent  Child1  Child2  Child3  Child4
1           A       B       C       Cc      C_C
1           A       B       C       Ccc     
1           A       D       Dd              
9           E       Ee      Eee

Upvotes: 1

Related Questions