Oracle sql - Generate full hierarchical chain from link table

Question

Supposing you have the following tables and data

create table articles (article_id number, name varchar2(30));
create table tags (tag_id number, parent_tag_id number, name varchar2(30));
create table associations (article_id number, tag_id number);

insert into articles values (1, 'item 1');
insert into articles values (2, 'item 2');
insert into articles values (3, 'item 3');

insert into tags values (100, null, 'parent');
insert into tags values (101, 100, 'child');
insert into tags values (102, 101, 'grandchild');
insert into tags values (103, null, 'another parent');

insert into associations values (1, 102);
insert into associations values (2, 101);
insert into associations values (3, 103);

The associations table links an article with the highest level tag associated with it. What is the most performant way to traverse tags and generate the full chain?

e.g. for the above data we should see

Article Name	Tag Name
item 1	parent
item 1	child
item 1	grandchild
item 2	parent
item 2	child
item 3	another parent

I've tried using connect by prior to generate the relationship between tags and parent tags, but am struggling to link the three tables together and persist the article name (I wondered about using connect_by_root to keep the name)

Answer 1

You can CROSS APPLY a correlated hierarchical query:

SELECT r.name AS article_name,
       t.name AS tag_name
FROM   articles r
       INNER JOIN associations a
       ON ( r.article_id = a.article_id )
       CROSS APPLY(
         SELECT name
         FROM   tags t
         START WITH t.tag_id = a.tag_id
         CONNECT BY PRIOR parent_tag_id = tag_id
       ) t

Which, for your sample data, outputs:

ARTICLE_NAME | TAG_NAME      
:----------- | :-------------
item 1       | grandchild    
item 1       | child         
item 1       | parent        
item 2       | child         
item 2       | parent        
item 3       | another parent

db<>fiddle here