CODEWITHSUNDEEP
sqloracle-databasehierarchical-dataconnect-by
krs8785
krs8785

Reputation: 1147

Hierarchial Query to return child rows SQL : Connect By

I have three table that look(just an example) as follows 

Artifact 
Id Name     
1  abc
2  xyz
3  pqr
4  mno

Classification 
Id Artifact_Id Node_id 
c1 1             n3
c2 1             n4
c3 3             n5
c4 4             n3
c5 4             n2

taxonomy_Node
Id  Parent_id
n1  null
n2  n1
n3  n1
n4  n2
n5  n3

So every artifact can have many classification(a hierarchy made of taxonomyNode i.e many nodes). Based on a taxonomyNode I want a list of artifacts that contain that node or its child. I am trying to return a list of artifact when they have classification belonging to a taxonomy_node. Also If the parent of any node is given as ID it should return the artifact containing the child node. I am not really clear here. Please let me know if you have any doubts.

select A.* from artifact A 
   inner join classification C 
    on A.id = C.ARTIFACT_ID
   inner join TAXONOMY_NODE T 
    on C.node_id=T.id
    where T.id = 5068
  START WITH T.ID = 5068
  CONNECT BY PRIOR T.ID = T.parent_id

For example look at classification table artifact 4 has two class c4 and c5 with node n2 and n3. So when I give node-id as n3 it should give me artifact 4 and also artifact 1 (as it contains n3) and also artifact 3 (as n3 is parent of n5). Similarly when i give node-id as n2 it should return 4 and 1

Upvotes: 0

Views: 183

Answers (1)

Multisync
Multisync

Reputation: 8797

/*
with Artifact as (
select 1 id,  'abc' name from dual
union all select 2,  'xyz' from dual
union all select 3,  'pqr' from dual
union all select 4,  'mno' from dual
),
Classification as (
select 'c1' id, 1 Artifact_Id, 'n3' Node_id from dual
union all select 'c2', 1, 'n4' from dual
union all select 'c3', 3, 'n5' from dual
union all select 'c4', 4, 'n3' from dual
union all select 'c5', 4, 'n2' from dual
),
taxonomy_Node as (
select 'n1' id, null Parent_id from dual
union all select 'n2',  'n1' from dual
union all select 'n3',  'n1' from dual
union all select 'n4',  'n2' from dual
union all select 'n5',  'n3' from dual
)
*/
select a.* 
from classification c 
     join (select id from taxonomy_node start with id = 'n3' connect by nocycle prior id = parent_id) h
     on (c.node_id = h.id)
     join artifact a on (a.id = c.artifact_id);

The problem with your query is that Oracle makes all joins first and then apply CONNECT BY.

Upvotes: 2

Related Questions