Using SQL to recursively search table for parent child relationships

Question

I have a table that contains the following data

ArticleID   publisherID
----------------------------
1001        9001
1001        9002
1001        9003
1002        9004
1002        9005
1003        9006
1004        9007
1005        9008
1006        9009
1007        9010
1007        9011
1007        9012
1008        9013
1008        9014
1009        9015
1010        9016
1011        9017
1012        9018
1012        9019
1012        9020
1012        9021
1012        9022
1012        9023
1013        9024
1013        9025
1014        9026
1014        9027
1014        9028
1015        9029
1015        9030

An article can be associated with more than one publisher. The publishers in that table could be related i.e one publisher can be a parent of another publisher. What i want to know is i want to list all the articles that are only related to publishers that are related. i.e. even though the data shows that the articles have different publishers the publishers are actually the same based on the publisher relationship table below:

publisherid publisher_parent_id
----------------------------------
9001            9001
9002            9001
9003            9001
9004            9004
9005            9005
9006            9006
9007            9007
9008            9008
9009            9009
9010            9011
9011            9011
9012            9011
9013            9014
9014            9014
9015            9015
9016            9016
9017            9017
9018            9035
9019            9035
9020            9035
9021            9035
9022            9035
9023            9035
9024            9025
9025            9025
9026            9026
9027            9027
9028            9028
9029            9030
9030            9030

Based on the above parent relationship i would like the output to be as shown below (i.e. list only the ones where the article is linked to more than one parent)

ArticleID   publisherID
----------------------------
1001        9001    (9001 is parent of 9002, 9003)
1007        9011    (9011 is parent of 9010, 9012)
1008        9014
1012        9035
1013        9025

A simple way to explain it is that i am trying to find out which of the articles are all related to the same publisher (The publisher is the same if they have the same parent).

I know how to join the tables but i am not knowledgeable enough to do the recursive search which is where i am struggling.

Another option i tried is to join the table with itself (providing a different alias) and doing where a.parent = b.parent and b.rownum <> a.rownum but i doubt that is the best way of doing it.

Thanks in advance

This might help http://stackoverflow.com/questions/4659803/recursion-in-oracle — Christian Phillips, Aug 04 '14 at 19:54
`rownum` is a resultset-based construct and cannot be used to uniquely identify a row. Using it in a comparison is generally a bad idea. — Allan, Aug 04 '14 at 20:30

JBC · Accepted Answer · 2014-08-04T20:06:11.977

My output doesn't exactly match yours and I'm pretty sure theres a better way to deal with the duplicates then the outer distinct I do, but I think this should get you close.

SELECT DISTINCT * 
FROM (
    SELECT ARTICLES.ARTICLEID, CONNECT_BY_ROOT PUBLISHERS.PUBLISHERID PUBLISHERID
    FROM ARTICLES
    INNER JOIN PUBLISHERS
      ON ARTICLES.PUBLISHERID = PUBLISHERS.PUBLISHERID
    WHERE LEVEL > 1
    START WITH PUBLISHERS.PUBLISHER_PARENT_ID = PUBLISHERS.PUBLISHERID
    CONNECT BY NOCYCLE PRIOR PUBLISHERS.PUBLISHERID = PUBLISHERS.PUBLISHER_PARENT_ID
    ORDER SIBLINGS BY PUBLISHERS.PUBLISHERID
)
ORDER BY ARTICLEID, PUBLISHERID

You can select this as a column in the inner query to see the leaves (the connection between publishers):

SYS_CONNECT_BY_PATH(PUBLISHERS.PUBLISHERID, ',') RELATED_PUBLISHERS

Using SQL to recursively search table for parent child relationships

1 Answers1