Zahid Zahid - 4 months ago 11
SQL Question

How to retrieve desired records?

Input Table : Regions

+---------------+---------------+---------- +-----------+
| Child | Parent | Level | levelname|
+---------------+---------------+---------- +-----------+
| All Region | All Region | 1 | national |
| Africa Region | All Region | 2 | region |
| America | All Region | 2 | region |
| Asia | All Region | 2 | region |
| Europe Region | All Region | 2 | region |
| Africa | Africa Region | 3 | Subregion |
| Asia Pacific | Asia | 3 | Subregion |
| Europe | Europe Region | 3 | Subregion |
| North America | America | 3 | Subregion |
| South America | America | 3 | Subregion |
| Argentina | South America | 4 | Country |
| Australia | Asia Pacific | 4 | Country |
| Pakistan | Asia Pacific | 4 | Country |
| South Africa | Africa | 4 | Country |
| Tunisia | Africa | 4 | Country |
| Uruguay | South America | 4 | Country |
+-------------------------------------------------------+


Here , regions are of 4 levels


  • All region

  • Region

  • Sub Region

  • Country

    they have 0,1,2 and 3 ancestors,such as a country has subregion,region and allregion as ancestors ,suppose we give "Uruguay" ,then output will be South America, America , All Region.



Now, I need a query for this table,which will retrieve all ancestors for a given "child"

Answer

Your best bet is a recursive CTE:

With recRegions AS
(
    /*Recursive Seed*/
    SELECT
        Child,
        Parent,
        Level
    FROM
        Regions
    WHERE 
        Child=<WhateverChildYouAreWanting>

    UNION ALL

    /*Recursive Term*/
    SELECT
        Regions.Child,
        Regions.Parent,
        Region.Level
    FROM
        recRegions
        INNER JOIN Regions on
            recRegions.parent = Regions.Child

)

Select Parent as Ancestors FROM recRegions;

Recursive queries can be a little tricky to wrap your head around at first, but if you break up the pieces of it, it makes sense:

  1. Recursive Seed - This is the part where we get the first term we are after. In your case, we just want the record where the Child is the country you are wanting to query.
  2. Recursive Term - This is the part where the query refers back to itself. It joins the recursive CTE recRegions to your Region table, connecting the child to the parent. The DB will hit this recursive term until no more records come back, which means we've climbed all the way up your hierarchy.
  3. The final select statement just pulls back the records from your recursive query. You wanted all the ancestors, so that would be all of the Parent field records.

Generally when you see a table with a layout child | parent | attributes | of | that | relationship you can turn to the super powerful recursive CTE to make quick sense out of it all.

As @dnoeth mentioned in your Q's comments, you could also join Regions table to itself 4 times since your hierarchy seems to be only 4 deep. A recursive query doesn't care about depth though, so if you add more depth to your hierarchy, you won't have to edit your SQL to pull the ancestors.