Hi @JediBrooker , The query that you could use should be something like this: WITH org_hierarchy_anchor AS (
SELECT
sup.organisation_id AS sup_id,
sup.organisation_name AS sup_name,
sub.organisation_id AS sub_id,
sub.organisation_name AS sub_name,
CAST(sub.organisation_name AS STRING) AS hierarchy_path
FROM organisation sup
JOIN relationship r ON sup.organisation_id = r.relationship_id
JOIN organisation sub ON r.relationship_orgid = sub.organisation_id
),
org_hierarchy_recursive AS (
SELECT
sup_id,
sup_name,
sub_id,
sub_name,
hierarchy_path
FROM org_hierarchy_anchor
UNION ALL
SELECT
oh.sup_id,
oh.sup_name,
s.organisation_id AS sub_id,
s.organisation_name AS sub_name,
CONCAT(oh.hierarchy_path, '->', s.organisation_name) AS hierarchy_path
FROM org_hierarchy_recursive oh
JOIN relationship r ON oh.sub_id = r.relationship_id
JOIN organisation s ON r.relationship_orgid = s.organisation_id
WHERE INSTR(oh.hierarchy_path, CAST(s.organisation_name AS STRING)) = 0
)
SELECT sup_id, sup_name, sub_id, sub_name, hierarchy_path
FROM org_hierarchy_recursive
ORDER BY sup_id, sub_id; Please, check the column names and table names, I just used what you have provided here. Let me know if works. Best, Cristian Barrueco
... View more
Hi, I'm CLEO! Something exciting is coming to the Community. Stay Tuned!
