Assistance to fix query to identify recursive relationships in data in SQL DW

Question

I have a table which has hierarchical relationships.

My input data is

product_identifier  parent_product_identifier  Zone
------------------  -------------------------  ----
1                   5                          E
2                   6                          F
3                   7                          G
4                   8                          H
5                   11                         R 
6                   12                         B
7                   13                         C
8                   14                         D
11                  15                         A

And expected output is :

product  parent_product_identifier  hierarchy  zone
-------  -------------------------  ---------  ----
1        5                          3          A
2        6                          2          B
3        7                          2          C
4        8                          2          D 
5        11                         2          A

I did try this approach :

with parent as (
  select
    product_identifier,
    parent_product_identifier,
    Zone,
    1 AS hierarchy,
  from
    temp
),
child as (
  select
    product_identifier,
    parent_product_identifier,
    Zone,
    p.hierarchy + 1,
  from
    temp c
  inner join
    parent p
  on
    c.parent_product_identifier = p.product_identifier
  and zone is not null
)
  select
    product_identifier,
    parent_product_identifier,
    Zone,
    hierarchy
  from
    parent
  union all
    select
    product_identifier,
    parent_product_identifier,
    Zone,
    hierarchy
  from
    child

But i am unable to achieve hierarchy of 3 how can i fix my query?

Note : my version of SQL DW does not support recursion. Is there any other way to do the same.

Please do not upload images of code/data/errors when asking a question. — Thom A
– Thom A ♦, Commented Apr 9, 2024 at 14:58
Now it looks better but your "expected" output is suspiciously like the actual outcome. It's supposed to be the what you want to get, not what you're getting — siggemannen
– siggemannen, Commented Apr 9, 2024 at 15:22
@ThomA, apologies, we have now question in format, as expected. — kumar talele
– kumar talele, Commented Apr 9, 2024 at 15:26

Ian Boyd · Accepted Answer · 2024-04-09 16:20:11Z

1

That query was a beast. There probably is a better way.

DBFiddle: https://dbfiddle.uk/jSdYcwWM

WITH dt AS (
  --add the root potion of the recursion
  SELECT
    temp.product_identifier,
    temp.parent_product_identifier,
    temp.Zone,
    1 AS hierarchy,
    parent_product_identifier AS current_parent --used to keep track as we walk the parents
  FROM temp

  UNION ALL

  SELECT
     dt.product_identifier, 
     dt.parent_product_identifier, 
     temp.Zone, 
     dt.hierarchy+1, --increase by one as we walk up the chain
     temp.parent_product_identifier AS current_parent
  FROM dt
     INNER JOIN temp
     ON temp.product_identifier = dt.current_parent
)
SELECT 
  product_identifier,
  parent_product_identifier,
  hierarchy,
  Zone
FROM dt
WHERE hierarchy > 1                                      --exclude rows that don't have a parent present
AND hierarchy = (
   SELECT MAX(hierarchy) FROM dt dt2 
   WHERE dt2.product_identifier = dt.product_identifier) --exclude rows we used along the way to build the recursion
ORDER BY product_identifier;

answered Apr 9, 2024 at 16:20

Ian Boyd

259k271 gold badges920 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kumar talele Over a year ago

Thanks for your answer but my version of SQL DW, does not support recursion. Is there any other way, i can do the same.

Ian Boyd Over a year ago

My first approach was going to be a table variable that you populate with the initial values from temp, and then use a WHILE loop to keep updated values in the table as you find their parents, and their parents, using their tier and increasing hiearchy by one each time, until @@rowcount = 0 meaning no more rows were affected by the update, and you've found no more parents.

kumar talele Over a year ago

I tried something on those lines but unable to achieve expected result. let me close this as you do have a solution , just not applicable in my case

kumar talele Over a year ago

I had a slight change in requirement also but updated question and my approach is here : stackoverflow.com/questions/78309647/…

Collectives™ on Stack Overflow

Assistance to fix query to identify recursive relationships in data in SQL DW

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related