Re: SQL question on hierarchical data

From: <eric__hogue_at_my-dejanews.com>
Date: 1998/10/07
Message-ID: <6vfr4u$24n$1@nnrp1.dejanews.com>#1/1

In article <HwAS1.2910$yo1.55067256_at_news.ipass.net>,   "David Sisk" <davesisk_at_ipass.net> wrote:
> I think you're going to have to switch back to ORACLE comrade! :)
>

I don't think this will be necessary.

> Sunil Godithi wrote in message ...
> >hi,
> >I have recently switched to SQL Server from oracle world and I am having
> >problem with Select statement.
> >
> >I have a table called emp with structure like:
> >EMPNO ENAME MGR
> >
> >MGR is the EMPNO of the manager. I need to get a list of employees like
 this
> >(select statement is Oracle specific, I need something similar for
> >SQLServer)...
> >SQL> l
> > 1 select level
> > 2 , ename
> > 3 , empno
> > 4 , mgr
> > 5 from emp
> > 6 connect by prior empno = mgr
> > 7* start with job = 'PRESIDENT'
> >SQL> /
> >
> > LEVEL ENAME EMPNO MGR
> > --------- ---------- ------- ---------
> > 1 KING 7839
> > 2 JONES 7566 7839
> > 3 SCOTT 7788 7566
> > 4 ADAMS 7876 7788
> > 3 FORD 7902 7566
> > 4 SMITHXX 7369 7902
> > 2 BLAKE 7698 7839
> > 3 ALLEN 7499 7698
> > 3 WARD 7521 7698
> > 3 MARTIN 7654 7698
> > 3 TURNER 7844 7698
> > 3 JAMES 7900 7698
> > 2 CLARK 7782 7839
> > 3 MILLER 7934 7782
> >
> >14 rows selected.
> >
> >Any help is appreciated..
> >

You can mention the table twice in a select statment, using different abbreviations (at least, in 11.5 on NT)

select e.level,
       e.ename,
       e.empno,
       m.empno as MGR
 from  emp e,
       emp m

 where e.mgr=m.empno

> >Sunil
> >

Eric

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Oct 07 1998 - 00:00:00 CDT