help with a SQL self-join

From: Edward Lock <ejlock_at_msn.com>
Date: Fri, 01 Mar 2002 10:53:23 -0800
Message-ID: <F001.0041D0AB.20020301105323@fatcity.com>

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). Received on Fri Mar 01 2002 - 12:53:23 CST