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 [EMAIL PROTECTED] _________________________________________________________________ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
