Help with Recursive query

[Leon1977]

I have a tree table with such columns
id, name, fk_id

where the "fk_id" column references the "id" column
I need a select statement that you get all branches by id
for example:

id name fk_id
--- ----- -----
1 one 1
2 two 1
3 three 1
4 four 2
5 five 2
6 six 5
so in this case if I choose 2
the query must return 2,4,5,6

 

[Scunningham99]

sorry not sure what you are doing here.... do you wish to display the id column where you put in a value for fk_id. And can you put in more than one value for fk_id?

Sy

 

[Leon1977]

this table draws a tree
"id" is the value for the branch
"fk_id" is its parent
so i want to get all "ids"(branches) to a certain id
so for example if
we have id=1
so we need all rows with fk_id=1
there is a row with id 2 and fk_id=1
then again we need all rows with fk_id=2
and so on..
i figure it out but software
I am curious is there a way I can make a recursive queries

 

[Scunningham99]

you could use a nested subquery or inline queries. however for this example i think you could get away with using a group by with an order by clause.

eg

select fk_id, name, id
from table1
group by fk_id, name, id
order by fk_id, name, id

i have not run this query but expect it should work!!
let me know.....sy

 

[Leon1977]

I am not sure i understand your logic but I run this and it shows all the rows in the table. I need to specify an starting "id" so maybe a where clause is needed
I do not need all of the rows just ones that are children to the specified one...

 

[Scunningham99]

ok, sorry thaught you wanted to display all rows

you can use the IN operator in the where clause of your query ie

select fk_id, name, id
from table1
where fk_id IN (1, 2, 3)
group by fk_id, name, id
order by fk_id, name, id

----IN (1,2,3) list all the parents you want to display with children!! simply put in the values you require.

you can also use ranges using the between clause!! let me know if this is required as i will provide syntax

 

[Leon1977]

ok. it works but only to the first level of the tree...
0-0 1
\ /
0-0-R-1-2-3
\ \ 0-0 1-2 4
0-0
ok if the selected id is (Marked with "R&quot
the last query returns the IDs to the first level (Marked with "1&quot I all sublevel IDs there could be "n" levels

 

[Scunningham99]

the reason it only returned 3 is because we put this in the IN clause ie IN (1,2,3). Instead we could have listed all the parents (fk_id) values. This is not good practice, and in our case as you say there can be "n" parents. I suggest using an subquery for example:

select fk_id, name, id
from table1
where fk_id IN (select fk_id from table1)
group by fk_id, name, id
order by fk_id, name, id

here we are using a subquery which select all the parent values, so in effect the query is dynamic, because as more parents are added the query will pick this up...

 

[Leon1977]

oks... it seems I can not explain well
look at the graph
I want to give to the query the ID of the (R) element
and i want this query to return to me the IDS of all children ids of the R element in this case the ids of all 1 level elements, 2 level, 3 level and 4 level
so I want to give 1 ID (R) and as a result to receive
7+1(R) 8 ids

 

[lewisp]

Is this what you want?

SELECT id,name
FROM table
CONNECT BY PRIOR fk_id = id
START WITH id = 2;