Geek asks and answers

Nested query is used provided - sql

I need to make a subquery in one table. Each row is potentially the parent or child of another row.

Is it possible to do this with a single select statement? Ive started using this operator, but it only drops one level.

select * from myTable where parent_id in 
   (select id from myTable where name = 'manager' )

This choice, however, will go only one level. If there are several children in a row, they will be ignored. (In the table, each row has an Id field , if there is a parent element in the row, then the parent Id value will be in the parent_Id child field .)

If I could include a while loop in SQL, which would always check whether the returned Id would be a parent or not, and if it is a check and see if there was any other row, its child would check the rest of the parent_Id lines . However, I think it will take many cycles to ultimately find all the parent child relationships. Any suggestions? Thanks

using Oracle db

+3
source share
1 answer

I think you are looking for a hierarchical query like this:

select * from mytable
connect by prior id = parent_id
start with name = 'Manager';

(A nested table is something else entirely.)

+3
source

More articles:


All Articles