help with hierarchical selects (SQL)

From: John Harper <harper_at_manitou.astro.utoronto.ca>
Date: 1996/02/21
Message-ID: <HARPER.96Feb21124328_at_manitou.utoronto.ca>#1/1

I'm trying to do a hierarchical selection from some ORACLE DB tables using SQL. The manual says do something like:

SELECT EMPNO, LEVEL FROM JOBS START WITH EMPNO < 400 CONNECT BY PRIOR EMPNO=SUP; (which works) where the table JOBS has columns EMPNO, SUP, + several others. However, the information I'm really interested in lives in another table PERSON (which has employee names etc), and these two tables are related via the EMPNO field (this is a RDBMS right?).

But the hierarchical select cannot do joins, so how can I extract the data from both tables in the proper order?

I don't want to have to create tempory tables etc (in hindsight I should not have made two tables, but, after all, this is a RDBMS right??)

Any help appreciated

John Harper

---

Administrative Programmer
Scarborough College
harper_at_lake.scar.utoronto.ca
<http://www.scar.utoronto.ca> Received on Wed Feb 21 1996 - 00:00:00 CET