Find all child nodes for any parent node in Oracle 10g

Question

My table structure is:

COMPANY_ID, ID, PARENT_ID

Here is some part from this table:

     | COMPANY_ID |     ID      |  PARENT_DID |
     |------------|-------------|-------------|
     | 12         |      1      |    null     |
     | 12         |      3      |     1       |
     | 12         |      2      |     1       |
     | 12         |      4      |     3       |
     | 12         |      7      |     4       |
     | 14         |      3      |     null    |

I want to find all child and grandchild IDs for any ID in Oracle 10g. The result must be like this for COMPANY_ID=12 and ID=3:

3, 4, 7

I have tried this, but this not working:

SELECT id
FROM TABLE_NAME
  START WITH ID        = 3
  CONNECT BY PARENT_ID = PRIOR ID
AND COMPANY_ID=12;

Alex Poole · Accepted Answer · 2015-03-05T18:20:09.293

4

You need to restrict it to the same company ID with another PRIOR clause, and move the specific company ID you're looking for into the START WITH clause:

SELECT id
FROM TABLE_NAME
START WITH ID = 3 AND COMPANY_ID = 12
CONNECT BY PARENT_ID = PRIOR ID AND COMPANY_ID = PRIOR COMPANY_ID;

        ID
----------
         3 
         4 
         7

If you specify multiple starting IDs (as you mentioned in a comment) you might get duplicates, which you could suppress with DISTINCT; but if you wanted to see which descendants were releated to which of your starting IDs, there's a handy connect_by_root operator that leats you keep track of that; for example:

SELECT CONNECT_BY_ROOT id AS root_id, id AS id
FROM TABLE_NAME
START WITH ID IN (3, 7)
AND COMPANY_ID = 12
CONNECT BY PARENT_ID = PRIOR ID and COMPANY_ID = PRIOR COMPANY_ID;

   ROOT_ID         ID
---------- ----------
         3          3 
         3          4 
         3          7 
         7          7

edited Mar 05 '15 at 18:20

answered Mar 05 '15 at 17:56

Alex Poole

183,384
11
179
318

Thanks very much. That's all. It works fine. Is there any problem if I want to retreieve child id's for more than one parent id like that: `START WITH ID IN (3,15,18)`? – Ezio Auditore da Firenze Mar 05 '15 at 18:09
@EzioAuditoredaFirenze - you could get duplicates depending on your structure, so you might need to add DISTINCT. Other than that I think that's fine. – Alex Poole Mar 05 '15 at 18:15
2

@EzioAuditoredaFirenze - I've added a note about another option that might be useful if you're starting with multiple IDs; depends if you'd want to know what was related to each one. – Alex Poole Mar 05 '15 at 18:21

Find all child nodes for any parent node in Oracle 10g

1 Answers1