The following is a simplified view of an Entity structure with an applicaiton.
FEntityID references itself which creates the tree. The status can be active, inactive, potential.
Lets call the relationship between a group of related entities an Entity Group.
Lets call the above group of records an entity group as they are all related. There can be several entity groups within this table.
The rule is that if the user wants to see all Active entities then if at least one entity within an entity group is active then the rest of the entity group should be displayed.
I'm trying to do this using a SQL query but my brain is failing and I require some assistance from the experts.