Nested query using while condition - sql

Question

I need to do a nested query on a single 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 statement but it only goes down one level.

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

This select however will only go down one level. If a row has multiple children they will be ignored. (In the table each row has an Id field , if a row has a parent then the parents Id value will be in the child's parent_Id field. )

If i could include a while loop in the SQL which would always check to see if the returned Id was a parent or not and if it was check and see if any other row was its child by checking the other rows parent_Id. However i m concerned this would take alot of cycles to eventually find all parent child relationships. Any suggestions? Thanks

using Oracle db

Which DB are using? Different DB's have different solutions for this particular problem. — Conrad Frix, May 23 '11 at 16:10
Could you give an example of a query you are trying to write - what fields you want returned for example. — BonyT, May 23 '11 at 16:13

score 3 · Accepted Answer · answered May 23 '11 at 16:20

3

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.)

answered May 23 '11 at 16:20

Tony Andrews

129,880
21
220
259

1

I'm going to assume that this is Oracle specific code for dealing with Hierarchies. SQL Server of course has its own methods. For a more generic approaches, check out http://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/1558609202/ref=sr_1_3?ie=UTF8&qid=1306168577&sr=8-3 – Tom H May 23 '11 at 16:36
@Tom, correct this is an Oracle-specific answer; the question is tagged for Oracle. – Tony Andrews May 23 '11 at 16:39
Thank you, this query will dive into every parent child relationship where it finds a value of "Manager" in a field named name, therefore if the value is not unique then i will start to see lots of duplicates. Thanks – cdugga May 24 '11 at 10:41

Nested query using while condition - sql

1 Answers1