I’ll try to explain with a little modification in your query. But your query is also using the same logic. Before continuing, let me tell you that you’ll not get the result if you have more data in your table with the same hierarchy. If you need to bring another row, you’ll have to add another sub query and so on. This means this query is not appropriate for what you want. I’ll try to explain with more data in the table than you have.

SCOTT @ORCL> select * from employees
2 order by eid;
EID ENAME MGRID
---------- ---------- ----------
1 Emp1 0
2 Emp2 1
3 Emp3 1
4 Emp4 2
5 Emp5 3
6 Emp6 4
7 Emp7 5
8 Emp8 6
9 Emp9 7
10 Emp10 8
11 Emp11 9
12 Emp12 10
12 rows selected.
SCOTT @ORCL> select * from employees t1
2 where mgrid = 2
3 or exists
4 (select * from employees t2
5 where eid = t1.mgrid
6 )
7 /
EID ENAME MGRID
---------- ---------- ----------
2 Emp2 1
3 Emp3 1
4 Emp4 2
5 Emp5 3
6 Emp6 4
7 Emp7 5
8 Emp8 6
9 Emp9 7
10 Emp10 8
9 rows selected.

Because you used exist operator and joined the outer query with the inner query, so it’ll exclude 0 and bring all the other rows.

SCOTT @ORCL> select * from employees t1
2 where mgrid = 2
3 or exists
4 (select * from employees t2
5 where eid = t1.mgrid
6 and mgrid = 2
7 )
8 /
EID ENAME MGRID
---------- ---------- ----------
4 Emp4 2
6 Emp6 4

Now suppose, in the inner query, you give the criteria where mgrid = 2, it’ll bring two rows because of the join condition. But if you change the mgrid from 2 to another mgrid then it’ll bring the row with respect to that mgrid because of the join condition. As the result in the below query shows.

SCOTT @ORCL> select * from employees t1
2 where mgrid = 2
3 or exists
4 (select * from employees t2
5 where eid = t1.mgrid
6 and mgrid = 3
7 )
8 /
EID ENAME MGRID
---------- ---------- ----------
4 Emp4 2
7 Emp7 5

Now change the mgrid and your result will be according to that mgrid.

SCOTT @ORCL> select * from employees t1
2 where mgrid = 2
3 or exists
4 (select * from employees t2
5 where eid = t1.mgrid
6 and mgrid = 4
7 )
8 /
EID ENAME MGRID
---------- ---------- ----------
4 Emp4 2
8 Emp8 6

So in your query, you are joining the table to outer query and moving forward row by row. However, with this approach you’ll not get the remaining rows in your table with the same pattern of data. To include the other rows, you’ll have to write more inner queries in your code. If you have 100 rows or even more than that, how can you write the query this way? Andrew gave you a good suggestion and I think I explained your query.

Check your query. It’ll not bring the remaining rows from the table because you don’t have sufficient sub queries to bring the remaining data.

SCOTT @ORCL> select eid
2 , ename
3 , mgrid
4 from employees t1
5 where mgrid = 2
6 or exists
7 ( select *
8 from employees t2
9 where eid = t1.mgrid
10 and (
11 mgrid = 2
12 or exists
13 ( select *
14 from employees t3
15 where eid = t2.mgrid
16 and mgrid = 2
17 )
18 )
19 )
20 /
EID ENAME MGRID
---------- ---------- ----------
4 Emp4 2
6 Emp6 4
8 Emp8 6

I hope this query will be cleared to you now.

Now check this query.

SCOTT @ORCL> SELECT eid,ename, mgrid , LEVEL
3 FROM employees
4 START WITH mgrid = 2
5 CONNECT BY PRIOR eid = mgrid
6 /
EID ENAME MGRID LEVEL
---------- ---------- ---------- ----------
4 Emp4 2 1
6 Emp6 4 2
8 Emp8 6 3
10 Emp10 8 4
12 Emp12 10 5

regards,

Saadat Ahmad
[Updated on: Sat, 16 August 2008 12:21]

Report message to a moderator