Home » SQL & PL/SQL » SQL & PL/SQL » exist in parent not in child
exist in parent not in child [message #218142] Tue, 06 February 2007 23:59 Go to next message
axast
Messages: 4
Registered: November 2003
Junior Member
Please help me with this query. I want to find all the entity which exist in Parent, but not in Child.


here are the tables:
Table L1	Table L2	Table L3	Table Hierarchy
Entity		Entity		Group		Group
		Group		State		ParentGroup
						Code

Joins:
L1.Entity = L2.Entity
L2.Group = L3.Group
L3.State = ‘NY’
And L3.Group =  Hierarchy.ParentGroup
And Hierarchy. ParentGroup <> 0

(SELECT     Hierarchy. ParentGroup, 
            Hierarchy. Group,
            Hierarchy.Code
 FROM Hierarchy 
 WHERE Hierarchy.Code = ‘ENT’
 CONNECT BY Hierarchy. ParentGroup = PRIOR Hierarchy. Group
 AND Hierarchy.CODE = PRIOR Hierarchy.CODE
 START WITH Hierarchy. ParentGroup = 0)

Table Hierarchy has records like this:

ParentGroup		Group
0	23061 – Highest Level always has parent zero
23061			230611
230161			230612
230611			230777
230611			230778
230612			230888
230612			230889


Let’s say this one ENTITY “901” exists in 230611(Grand parent) and 230611(parent), but not in any grandchildren.
The entries in L2 look like this:
Entity Group
901 23061
901 230611
------

We want to report all Parent which have missing entity in CHILD
In this case, we only want to report 230777.

The reason we don’t report 230612 is because it still exists in 230611 (as long as one of the child of 23061 has it), it is ok. But when it comes to 230611 and 230612 being parent, it doesn’t exist in any children of theirs. So we want to report one (or all) children of 230611.

For some reason, this is not happening with my query. PLEASE HELP.

[Updated on: Wed, 07 February 2007 08:15]

Report message to a moderator

Re: exist in parent not in child [message #218247 is a reply to message #218142] Wed, 07 February 2007 08:09 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Quote:
We want to report all Parent which have missing entity in CHILD
In this case, we only want to report 230777.

The reason we don’t report 23062 is because it still exists in 23061 (as long as one of the child of 23061 has it), it is ok. But when it comes to 230611 and 230612 being parent, it doesn’t exist in any children of theirs. So we want to report one (or all) children of 230611.


I have no idea what you are talking about. Where are 901 and 23062 in your sample data ?
Re: exist in parent not in child [message #218249 is a reply to message #218247] Wed, 07 February 2007 08:18 Go to previous messageGo to next message
axast
Messages: 4
Registered: November 2003
Junior Member
I made changes to my original message. Hopefully it is a little better now. Sorry about the confusion. What I want to say is ....
1. List Entities which have a Parent and are missing in ALL of the children of that parent.
2. If an Entity exists in Parent, and atleast one of the children, we do NOT want to report it.
3. The query should be looking at all levels of Parents, not just the top most level.
Re: exist in parent not in child [message #218257 is a reply to message #218142] Wed, 07 February 2007 08:39 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Still none the wiser. So is 901 both an entity and a group ? You have it as a child of 23061, which is the highest level record, and also of 230611, which is an intermediate level record.

1. List Entities which have a Parent and are missing in ALL of the children of that parent.
If the entity has a parent, then it is itself a child. How therefore can it be missing in all in the children of that parent ?

Previous Topic: Need quick help with a Max Query
Next Topic: Debugging
Goto Forum:
  


Current Time: Sat Dec 10 14:52:38 CST 2016

Total time taken to generate the page: 0.08059 seconds