Home » SQL & PL/SQL » SQL & PL/SQL » can some explain me in detailed how the below query works (oracle 9i)
can some explain me in detailed how the below query works [message #341041] Fri, 15 August 2008 13:00 Go to next message
sivaram28
Messages: 3
Registered: August 2008
Location: India
Junior Member

I want to display all the employees who report to a person. My data is like a tree structure.
eid   ename   mgrid
 1    Emp1     0
 2    Emp2     1
 3    Emp3     1
 4    emp4     2
 5    emp5     3
 6    emp6     4

For example, if I give eid as 2, I need report as:

eid   ename   mgrid
 4    emp4     2
 6    emp6     4

I need this data in the report because
- eid 4's mgrid is 2
- eid 6's mgrid is 4.

How to do that using SQL?

I got the sql during my search and the query is
select eid
     , ename   
     , mgrid
  from employees as t1
 where mgrid = 2
    or exists 
       ( select * 
           from employees as t2
          where eid = t1.mgrid 
            and (
                mgrid = 2 
             or exists 
                ( select * 
                    from employees as t3
                   where eid = t2.mgrid 
                     and mgrid = 2 
                )                 
                ) 
       ) 


Can some body explain how this query is working ?

[Mod-Edit: Frank added [code]-tags to improve readability]

[Updated on: Fri, 15 August 2008 14:25] by Moderator

Report message to a moderator

Re: can some explain me in detailed how the below query works [message #341043 is a reply to message #341041] Fri, 15 August 2008 13:52 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Are you specifically avoiding "connect by" for some reason?
http://philip.greenspun.com/sql/trees.html
http://www.orafaq.com/node/2038
Re: can some explain me in detailed how the below query works [message #341098 is a reply to message #341041] Sat, 16 August 2008 07:38 Go to previous messageGo to next message
sivaram28
Messages: 3
Registered: August 2008
Location: India
Junior Member

With out Using connect by Prior,can some body give me a query ,or explain the above query
Re: can some explain me in detailed how the below query works [message #341120 is a reply to message #341098] Sat, 16 August 2008 12:08 Go to previous message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

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

Previous Topic: Updating one table from other two tables
Next Topic: Parent - Child query
Goto Forum:
  


Current Time: Sun Dec 04 14:38:58 CST 2016

Total time taken to generate the page: 0.05233 seconds