| Hierarchy remove [message #39732] |
Tue, 06 August 2002 01:23  |
Bryan Avery
Messages: 4
Registered: July 2002
|
Junior Member |
|
|
I have a table which has a hierarchy structure ,like this
Child Parent
1 0
2 0
3 2
4 2
5 3
6 3
7 3
8 4
9 5
10 1
Another table which has a link to the hierarchy table
Product Child
Sweet 8
Boot 8
Sock 7
Car 10
So it looks something like this
1-
--10-
-----Car
2-
--3-
----5-
------9
----6
----7-
------Sock
--4-
----8-
------Sweet
------Boot
Quite simple.
Now the problem, as you can see the tree from 1-10 has a car, 2-3-7 has Sock and 2-4-8 has Sweet and Boot.
But 2-3-5-9 and 2-3-6 have no products.
What I need to be able to do is remove the items so that every tree has a product. What you should be left with is:
1-
--10-
-----Car
2-
--3-
----7-
------Sock
--4-
----8-
------Sweet
------Boot
So removing 5, 6 and 9 from the structure, but still keeping 2 and 3.
This is driving me mad and got me going in circles.
|
|
|
|
| Re: Hierarchy remove [message #39739 is a reply to message #39732] |
Tue, 06 August 2002 14:06  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Bryan, hopefully this gives you a start:
sql>select * from h order by parent, child;
CHILD PARENT
--------- ---------
1 0
2 0
10 1
3 2
4 2
5 3
6 3
7 3
8 4
9 5
10 rows selected.
sql>select * from product order by child;
NAME CHILD
---------- ---------
Sock 7
Sweet 8
Boot 8
Car 10
sql>select h.parent, h.child, p.name
2 from (select parent, child
3 from h
4 where parent <> 0
5 start with parent = 0
6 connect by prior child = parent) h, product p
7 where p.child (+)= h.child
8 order by h.parent, h.child;
PARENT CHILD NAME
--------- --------- ----------
1 10 Car
2 3
2 4
3 5
3 6
3 7 Sock
4 8 Sweet
4 8 Boot
5 9
9 rows selected.
sql>delete
2 from h
3 where parent <> 0
4 and (parent, child) not in
5 (select parent, child
6 from h
7 start with child in
8 (select distinct child
9 from product)
10 connect by prior parent = child);
3 rows deleted.
sql>select h.parent, h.child, p.name
2 from (select parent, child
3 from h
4 where parent <> 0
5 start with parent = 0
6 connect by prior child = parent) h, product p
7 where p.child (+)= h.child
8 order by h.parent, h.child;
PARENT CHILD NAME
--------- --------- ----------
1 10 Car
2 3
2 4
3 7 Sock
4 8 Sweet
4 8 Boot
6 rows selected. |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
