How do I get just one set?

I am joining two tables in an SQL query.
Table 1: item has three fields, id, org_id, and name
Table 2: item_set has five fields item_id, org_id, leftIndex, depth, and
rightIndex
The item_set table is used to help sort structures hierarchically.
For example the following query would return a parent item and all its
children starting at the top of a tree:
Select
Itm1.id as parent_id,
Itm1.name as parent_name,
Is1.leftIndex as parentIndex,
Itm2.id as child_id,
Itm2.name as child_name,
Is2.leftIndex as childIndex,
Is2.depth as degree_of_separation
From
item as itm1
Inner join
Item_set as is1
On (itm1.id = is1.item_id and is1.depth = 0)
And (itm1.org_id = is1.org_id)
Inner join
Item_set as is2
On (is2.leftIndex between is1.leftIndex and is1.rightIndex)
And (is1.item_id <> is2.item_id)
And (is1.org_id = is2.org_id)
Inner join
Item as itm2
On (is2.item_id = itm2.id)
And (itm2.org_id = is2.org_id)
order by itm1.id , itm2.id
The trouble is that there may be different representations of trees for the
same items in the item_set table. One representation of four items might
have leftIndex values 1, 2, 3, 4 for the parent and its three children and
there could be another set of leftIndex values of 20, 21, 22, 24 for the
very same set of items.
My question for the SQL experts out there is how do I write a query to get
only one set of the items such as 1, 2, 3, 4 ? I can't assume that the
sequence will always be starting at the top of the tree, e.g. depth = 0.
27-1006Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Also, rows are not records and columns are not fields, there is not
such things as a magical "id", a vague "name", etc. Read ISO_11179 for
the proper way to name things
CREATE TABLE Items
(item_id INTEGER NOT NULL ,
org_id INTEGER NOT NULL,
PRIMARY KEY(item_id, org_id),
item_name CHAR(15) NOT NULL);
Is this a nested sets model!
CREATE TABLE ItemSets
(item_id INTEGER NOT NULL
org_id INTEGER NOT NULL,
FOREIGN KEY (item_id, org_id),
REFERENCES Items (item_id, org_id)
ON DELETE CASCADE -- guess at biz rule
ON UPDATE CASCADE,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0) ,
rgt INTEGER NOT NULL UNIQUE,
CHECK (lft < rgt));
depth is computable, so do not store it. It will get out of synch and
screw up things.
Get a copy of TREES & HIERARCHIES IN SQL and look at chapter about
compare sub-tree structures. I am not going to give you a few
thoiusand words and illustrations in a newsgroup.
The basic idea is pick the root node. find the first subtree and
substract MIN(lft) from the lft and rgt values. Find the second
subtree and repeat the process. UNION ALL the two canonical subtrees
1) if you have a table with exactly duplicated rows. the subtress are
identical
2) if the nodes match, but not the (lft, rgt) pairs, they are different
arrangements of the same nodes
3) If the nodes do not match, but the (lft, rgt) pairs do, the have the
same structure with different nodes.
The pictures will help when you buy the book.
Here is query for depth. Assume an organizational chart in a Nested
Set model.
SELECT COUNT(O2.emp) AS depth, O1.emp
FROM OrgChart AS O1, OrgChart AS O2
WHERE O1.lft BETWEEN O2.lft AND O2.rgt
GROUP BY O1.lft, O1.emp;