CODEWITHSUNDEEP
oracle-databaseplsqloracle10gplsqldeveloper
Ezio Auditore da Firenze
Ezio Auditore da Firenze

Reputation: 1226

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

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;

Upvotes: 2

Views: 2861

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

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

Upvotes: 4

Related Questions