Home » SQL & PL/SQL » SQL & PL/SQL » Tree Structure query
Tree Structure query [message #188145] Thu, 17 August 2006 05:10 Go to next message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
Hi friends,

I have a table like this

ename empno mgr

a 10
b 20 10
c 30 40
d 40 20
e 50 30

if i give 20 as a mgr

it s empno 40 and its mgr number is 30 and its mgr number is 50

if input is 20 the output should be like this:
40
30
50

Thanks and Regards
Selva.R
c
Re: Tree Structure query [message #188146 is a reply to message #188145] Thu, 17 August 2006 05:16 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
SELECT ename
     , empno
FROM   ( SELECT 'a' ename, 10 empno, NULL mgr FROM dual UNION ALL
         SELECT 'b' ename, 20 empno, 10   mgr FROM dual UNION ALL
         SELECT 'c' ename, 30 empno, 40   mgr FROM dual UNION ALL
         SELECT 'd' ename, 40 empno, 20   mgr FROM dual UNION ALL
         SELECT 'e' ename, 50 empno, 30   mgr FROM dual 
       ) yourtable
CONNECT BY prior empno= mgr
START WITH mgr = 20
/


MHE
Re: Tree Structure query [message #188147 is a reply to message #188146] Thu, 17 August 2006 05:28 Go to previous messageGo to next message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
no i dnt want answer like this,

Can you give me some other option
Re: Tree Structure query [message #188150 is a reply to message #188147] Thu, 17 August 2006 05:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
selvakumar_82 wrote on Thu, 17 August 2006 12:28

no i dnt want answer like this,

Can you give me some other option
Dognabbit, quite demanding are we? What option did you have in mind? Give some more information. If you have a supported Oracle version this will work and it is the standard Oracle way to create a hierarchical query. Sigh.

MHE
Re: Tree Structure query [message #188151 is a reply to message #188147] Thu, 17 August 2006 05:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, you could let us know what's wrong with @Maaher's answer, and then we could try and correct that.

Or, we could just produce answers until we eventually hit one you like?

Which of these works better for you?

Is this acceptable?
SQL> SELECT empno
  2  FROM   ( SELECT 'a' ename, 10 empno, NULL mgr FROM dual UNION ALL
  3           SELECT 'b' ename, 20 empno, 10   mgr FROM dual UNION ALL
  4           SELECT 'c' ename, 30 empno, 40   mgr FROM dual UNION ALL
  5           SELECT 'd' ename, 40 empno, 20   mgr FROM dual UNION ALL
  6           SELECT 'e' ename, 50 empno, 30   mgr FROM dual 
  7         ) yourtable
  8  CONNECT BY prior empno= mgr
  9  START WITH mgr = 20;

     EMPNO
----------
        40
        30
        50
(I just removed the ENAME column, so that the output EXACTLY matches the output you asked for).
Re: Tree Structure query [message #188154 is a reply to message #188150] Thu, 17 August 2006 05:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd lend you my Acme Psychic Helmet, but the battery's flat.
Re: Tree Structure query [message #188160 is a reply to message #188145] Thu, 17 August 2006 05:53 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
I suspect that the inline view has confused the OP.

@Selva.R: Do you like this answer?
SELECT empno
FROM  <enter_employee_tablename>
CONNECT BY prior empno= mgr
START WITH mgr = <enter_input_mgr_number>
/

Re: Tree Structure query [message #188166 is a reply to message #188160] Thu, 17 August 2006 06:04 Go to previous messageGo to next message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
Hi,

I was highly disappointed with Mr.Mahee answers,

In Inline view you are mentioning table datas,

If you dnt know the values in the table how to get the values.....?
(i want query to work dynamic)

thats my problem

More over, I used

SELECT empno
FROM <enter_employee_tablename>
CONNECT BY prior empno= mgr
START WITH mgr = <enter_input_mgr_number>

It is returning only one row,

Any other help will be useful for me

Re: Tree Structure query [message #188169 is a reply to message #188166] Thu, 17 August 2006 06:13 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
select empno from dognabbit
start with mgr = 20
connect by prior empno = mgr;

You may want to read up on inline views to understand Maaher's query !!

[Updated on: Thu, 17 August 2006 06:14]

Report message to a moderator

Re: Tree Structure query [message #188171 is a reply to message #188166] Thu, 17 August 2006 06:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You have no grounds for disappointment.
@Maahers query answered your problem perfectly, and the fact that it either doesn't meet requirements you didn't mention, or that you just don't understand it is hardly his problem.

The part of the view with all the UNION ALL statements in it
( SELECT 'a' ename, 10 empno, NULL mgr FROM dual UNION ALL
  3           SELECT 'b' ename, 20 empno, 10   mgr FROM dual UNION ALL
  4           SELECT 'c' ename, 30 empno, 40   mgr FROM dual UNION ALL
  5           SELECT 'd' ename, 40 empno, 20   mgr FROM dual UNION ALL
  6           SELECT 'e' ename, 50 empno, 30   mgr FROM dual 
  7         ) yourtable

is simply a shortcut to avoid creating a table and populating it.
If you'd bothered to provide a CREATE TABLE and INSERT script like the Sticky at the top of the forum asks for, then we'd have used that instead. As it is, this is easier and quicker to code for us.

You will notice that if you run this part of the query as a stand alone SQL, it returns exactly the same data that you specified in your OP.

SQL> SELECT 'a' ename, 10 empno, NULL mgr FROM dual UNION ALL
  2  SELECT 'b' ename, 20 empno, 10   mgr FROM dual UNION ALL
  3  SELECT 'c' ename, 30 empno, 40   mgr FROM dual UNION ALL
  4  SELECT 'd' ename, 40 empno, 20   mgr FROM dual UNION ALL
  5  SELECT 'e' ename, 50 empno, 30   mgr FROM dual ;

E      EMPNO        MGR
- ---------- ----------
a         10
b         20         10
c         30         40
d         40         20
e         50         30


From what you say, it sounds like your EMP table either doesn't have the data in it that you expect, or you've made a typing mistake somewhere.

Run the query
SELECT empno
FROM <enter_employee_tablename>
CONNECT BY prior empno= mgr
START WITH mgr = <enter_input_mgr_number>
again in SQL*Plus, and cut and paste the query and the results, so we can see what's happening.


Re: Tree Structure query [message #188172 is a reply to message #188169] Thu, 17 August 2006 06:16 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
"Dognabbit" should be "dagnabbit". I was referring to a certain mister Bundy I remember from my childhood.

MHE
Re: Tree Structure query [message #188178 is a reply to message #188172] Thu, 17 August 2006 06:31 Go to previous message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member

Thanx for your wonderful support


Thanx and Regards
Selva.R



Previous Topic: Reports in SQL
Next Topic: pcl commands
Goto Forum:
  


Current Time: Fri Dec 02 18:40:32 CST 2016

Total time taken to generate the page: 0.40194 seconds