Oracle-L: RE: help with a SQL self-join

From: Pardee, Roy E <roy.e.pardee_at_lmco.com>
Date: Mon, 04 Mar 2002 07:38:25 -0800
Message-ID: <F001.0041E440.20020304073825@fatcity.com>

If I understand correctly, it sounds like you need a *recursive* self-join. Depending on the version of your db (certainly in 8i or later) you may be able to use the CONNECT BY PRIOR syntax to get what you're after.

Have a look at the SQL reference for 'hierarchical queries'. Here's a link to the 9i docs:

http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/qu eries2.htm#2053937

(please watch for line wraps)

for reference.

HTH,
-Roy

Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487

-----Original Message-----

Sent: Friday, March 01, 2002 10:53 AM
To: Multiple recipients of list ORACLE-L

I need help with a self-join. I have two tables: DEPT_TBL and TREENODE.

One table holds dept data. Depts report to one another at at different levels, and are "rolled-up" for different purposes. This roll-up information is stored in the TREENODE table.

Each tree_node in TREENODE has a corresponding PARENT_TREE_NUM, which corresponds to it's roll-up level. DEPT_TBL holds dept ID and dept description, but no level information. TREENODE holds tree_descriptions.

The a.deptid = b.tree_node, so that's a possible join.

Question: How do I pull a dept ID and it's corresponding roll-up node/deptid?

This is what I have so far;

select DISTINCT a.tree_node DEPT, b.tree_node COLL from treenode a, treenode b
where a.tree_node <> b.tree_node

and a.tree_name = 'DEPT_SECURITY'
and a.tree_node = 'H0086'
and a.parent_node_num = b.parent_node_num;

....this pulls a dept and all the depts that roll-up at the same level, but not the roll-up level. For example, Dept 'H0086', and 27 depts that rollup at the same level. I simply want a single row with two columns: a deptID,

and a rollup ID.

How do I do this?

Any help is really appreciated.

Thanks--
Edward Lock
ejlran_at_hotmail.com

MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Edward Lock
INET: ejlock_at_msn.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------


To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Pardee, Roy E
INET: roy.e.pardee_at_lmco.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------


To REMOVE yourself from this mailing list, send an E-Mail message