Recursive Query Challenge - Simple Parent/Child Example

Recursive query challenge - simple parent/child example

With help from RhodiumToad on #postgresql, I've arrived at this solution:

WITH RECURSIVE node_graph AS (
    SELECT ancestor_node_id as path_start, descendant_node_id as path_end,
           array[ancestor_node_id, descendant_node_id] as path 
    FROM node_relations

    UNION ALL 

    SELECT ng.path_start, nr.descendant_node_id as path_end,
           ng.path || nr.descendant_node_id as path
    FROM node_graph ng
    JOIN node_relations nr ON ng.path_end = nr.ancestor_node_id
) 
SELECT * from node_graph order by path_start, array_length(path,1);

The result is exactly as expected.

Recursive query for postgresSQL parent/child

A recursive query is not needed to find the deepest children. Instead, one would look at entries that are not a parent (so no other child exists). Such entries ID is not included in the parent column.

You can then join this categories to other tables

SELECT *
FROM category cat
  JOIN category_dictionary cat_dic ON cat.id = cat_dic.id
WHERE NOT EXISTS
 (SELECT 1 FROM category cat2
  WHERE cat2.parent = cat.id);

Snowflake CTE repeat each parent and child row of all possible combinations

Using "breadcrumb" array as helper structure to determine position in the path(naive approach):

WITH RECURSIVE cte AS (
   SELECT dt.*, tml.parent_territory_key
   FROM dimension_territory dt 
   LEFT JOIN territory_member_list tml
    ON tml.child_territory_key = dt.territory_key
), rec AS (
   SELECT cte.TERRITORY_KEY, cte.TERRITORY_NAME, cte.parent_territory_key,
          ARRAY_CONSTRUCT(cte.TERRITORY_KEY) AS a, 0 AS lvl
   FROM cte
   WHERE PARENT_TERRITORY_KEY IS NULL
   UNION ALL
   SELECT cte.TERRITORY_KEY, cte.TERRITORY_NAME, cte.parent_territory_key,
          ARRAY_APPEND(rec.a, cte.TERRITORY_KEY), lvl+1
   FROM rec
   JOIN cte ON rec.TERRITORY_KEY = cte.parent_territory_key
), lognest_path AS (
   SELECT * FROM rec QUALIFY lvl = MAX(lvl) OVER()
), cartesian AS (
  SELECT dt1.TERRITORY_KEY AS ANCESTOR_KEY, dt1.TERRITORY_NAME AS ANCESTOR_NAME,
         dt2.TERRITORY_KEY AS DESCENDANT_KEY, dt2.TERRITORY_NAME AS DESCENDANT_NAME
  FROM dimension_territory dt1
  CROSS JOIN dimension_territory dt2
)
SELECT DISTINCT c.*, lp.a,
      ARRAY_POSITION(c.ancestor_key,lp.a) AS a_p, 
      ARRAY_POSITION(c.descendant_key, lp.a) AS d_p,
      a_p = 0 AND d_p = 0 AS IS_ROOT,
      d_p - a_p AS EDGE_DISTANCE
FROM cartesian c
JOIN lognest_path lp 
  ON ARRAY_POSITION(c.ancestor_key,lp.a) >= 0
 AND ARRAY_POSITION(c.descendant_key, lp.a) >=0
WHERE a_p <= d_p
ORDER BY ANCESTOR_KEY, DESCENDANT_KEY;

Output:

SQL Query Parent Child Full Path from table

You could use INNER JOIN and LEFT JOIN if you are using SQL SERVER MS, and here are how the query look like, which will give you the full result (combination) based on your requirement:

SELECT A.ParentTitle + '\'+B.ParentTitle+ 

                                         CASE WHEN C.ParentTitle IS NOT NULL THEN '\' +C.ParentTitle
                                         ELSE ''
                                         END
     +
     ' =' + A.ParentID + '\'+B.ParentID+ 

                                         CASE WHEN C.ParentID IS NOT NULL THEN '\' +C.ParentID
                                         ELSE ''
                                         END

FROM TABLE AS A 
INNER JOIN TABLE AS B
ON B.ParentID = A.ChildId
LEFT JOIN TABLE AS C
ON C.ParentID = B.ChildId

Not 100% sure whether it will work as I expected or not, please give it a try xD

SSRS Hierarchy Recursive parent - child with multiple parent

After reading hours of article. I come to the conclusion that SSRS is not able to achive my end goal. sad I know. but it is what it is

https://connect.microsoft.com/SQLServer/feedback/details/724449/bug-found-when-using-the-ssrs-recursive-hierarchy-feature-with-multi-parent-relationships

Bug Found When using the SSRS Recursive Hierarchy Feature with
Multi-Parent Relationships - by MichaelLee

Status : Closed as By Design By Design

The product team
believes this item works according to its intended design.


A more detailed explanation for the resolution of this particular item
may have been provided in the comments section.

Description

In a SSRS report, you can create a recursive hierarchy with drill down
controls by following this article:
http://msdn.microsoft.com/en-us/library/bb630438(v=SQL.100).aspx

However, it seems that this feature does not work correctly when a
child has multiple parents. If a child has two parents, you would
expect that a child is placed under each parent. Instead, the child is
placed only under the parent that appears first in the SQL table. If
you add an additional column to the tablix and set the expression to
'=CountRows("RowGroupName",Recursive)', you'll notice that the total
records for the child is 2. So for some reason both records are being
placed below the first parent, even though one of the records has a
different parentid.

Note that in my situation, the child and parent ids are of the type
uniqueidentifier.

DETAILS Comments (2) | Workarounds (3) | Attachments (0) Sign
in to post a comment. Posted by Ibrahim Achkar on 4/20/2012 at 6:10 AM
Hello Michael, I also found this bug. Any update concerning a
resolution from Microsoft. As for your workaround, do you suggest
getting the data and setting it in a new temp table in a different
(1-to-1 relationship) format. With the new table at hand, do we still
need to use the parent grouping in SSRS or another method is required?

Your assistance is highly appreciated.

Thank you, Ibrahim Posted by Riccardo [MSFT] on 2/13/2012 at 5:34 PM
Thanks for your feedback. We're resolving this bug as By Design
because we associate each group with a single parent group. We don't
associate a group with multiple parent groups (but feel free to create
a Suggestion for this capability).

To further explain the expected behavior, we first group the data
based on the GroupExpression, or the ChildID field in this case. Then
we evaluate the Parent expression, in this case the ParentID field,
for each group. Since we associate each group with a single parent
group, we need a single value. When an expression that should return a
single value refers to fields in a scope with multiple data rows -
this case is just one example - the behavior is officially undefined,
but in practice, it tends to behave like the First aggregate function,
which takes the first data row in scope.

Riccardo Muti SQL Server Reporting Services

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a65bf4a4-e3b6-4c33-aa9e-6f7d7e4b7f5e/bill-of-materials-recursive-parent-report?forum=sqlreportingservices

Hi duanekae, Just to confirm, I never had an adequate solution using
SSRS for this. I have ended up using a custom SQL function to do this.
I would love the get it working in SSRS though as this would be a much
easier solution. Wednesday, January 13, 2016 4:04 PM Quote Avatar of
Alex Lush - Severn Unival Alex Lush - Severn Unival

---

Related Topics