Join a table to itself

[barbarao]

Hi,

I have a table that stores varies entities. An entity can own one or more entieis. I also have a table for relationships that list the type of relationship and parent ID and child ID. Do I use a self join with entity table and the relationship table in my query to get results or is there a better approach? thanks.

[Rabbit]

You would join the entity table to the relationship table twice.

[barbarao]

@Rabbit
That is what I thought. Just wanted to see if someone agreed with my approach. Thanks as usual and happy new year.

[Rabbit]

No problem. Happy new year to you too!

[barbarao]

Hi. Sorry for the delay (death in family). I did what you suggested but having a problem. I have Entities named Caesar, Kathy, susan, Michael, and Blake. Caesar and Kathy are both at the top of the food chain. They are ultimate owners if you will. Caesar owns Susan who owns Blake and Blake owns Michael. Kathy owns Pam. Caesar also guides Button but does not own Button. The only way that I can tell all that is by carefully studying the query results which would take forever with the amount of records in the Entity Table. Any thoughts on what I could do? I know I am stressed out so maybe I am just overlooking something. Thanks in advance.

[Rabbit]

If you are looking to store a hierarchical structure and want to be able to retrieve the hierarchical tree, then you need to use a different structure.

If there can ever only be one parent node, i.e. one owner or one guide and never more than one owner/guide in any combination, then I suggest the modified preorder tree traversal structure.

You can read the particular details about it from this article: http://www.sitepoint.com/hierarchical-data-database-2/

[barbarao]

@Rabbit
Thanks for the link. I will read it now. There can be only one ultimate owner, a next lever owner, one level after that, and probably one more. There are some entities related to things that are not owners and these may or may not have an ultimate ownder.

[Rabbit]

That's an issue then, the strucure I linked only works where each node only has one parent node. It sounds like one node may have one owner parent node and another non-owner parent node.

If this is true, then I can't think of an easy structure that you can use. You would have to do some sort of recursion to be able to pull the entire structure and that's not something you would be able to do easily in SQL if at all.

[barbarao]

Thanks again. The only thing I can think of would be in the relationship table have an OWN1, OWN, OWN3 assigned to each entity then add a new field for Ultimate and enter the ultimate owner there. I'll keep research. Thank again.

[barbarao]

Hi. Just wanted to close the loop in case anyone finds themselves in this situation. Found a model, forgot what it is called, that offered the idea of a "depth" field to enter the level of who own what. For example, entity Caesar has an ID of 10. He is the highest. Susan reports to him and her ID is 2 so her depth is 10 2. Blake reports to Suan and his ID is 7 so his depth is 10 2 7. Gives me the ability to see an entire "family" for lack of a better word just by sorting by ID field. thanks again.

[Rabbit]

How does the depth field represent a node that has an owner node and a guide node?

[barbarao]

@Rabbit
I thought that the 10 2 7 for the company "Blake" would indicated that he is 7, his immediate owner is 2, and the owner of 2 is 10. You don't think that would work? Guess I don't understand the node concept. Thanks in advance.

[Rabbit]

That shows one person reporting to one higher person. From your earlier statements, it's not always one to one. You said that they can have both an owner and a guide, which means one person reporting to two people one level higher.

Your 10.2.7 would be represented graphically like this:

10
 |
 2
 |
 7
 

Whereas what you described earlier is more like this:

11  3
 \ /
  8
 

[barbarao]

yes but each guide would be a would be a differnt depth path and they would not have OWN aa a relationship type so i think this is the best I can come up with. Buidling a solution while mourning my only brother and supporting an 87 years old mother who lives 900 miles away isn't easy. This request came at an awful time for clear thinking so your help has been more than appreciated.

[Rabbit]

I'm sorry for your loss.

The relationship type doesn't matter to the preorder tree structure, only that there is one and only one parent node at the next level up. If this is true, then I hold that my original suggestion would be the best structure to use. It allows you to insert nodes at any point with less work than it would take to do in any other paradigm, including the depth field you want to use. It also allows for easy retrieval, deletion of nodes, and movement of nodes. Each would only require a couple of queries that can be automated where as the depth field would require extensive manual editing to do additions, deletions, and movements.

If, however, your structure is static and you never have to move, delete, or add additional nodes, then the depth field is workable.

[barbarao]

Thanks. It is a hard loss for me as he was also my best friend throughout our lives.

Entity Susan, for example, who is owned by Entity Caesar, may have 4 guides as well. Entity Blake who is owned by Entity Susan, may have the same guides, different guides, or no guides at all. Entity Caesar, and all the other Entities like that one who are at the top of their family tree are not owned by another Entity nor do they have guides.

Entity Trevor may be self owned and have no or many guides.

I need to report not only on the full family tree but also generate reports on each type of guide.

Ownerships do not change often if at all.

I read through your article many times and sketched it out and just can't see how it will save time in building the solution or running any reports but I will read it again. Perhaps this time it will dawn on me but till then, I am going to keep the depth field in mind as a potential answer.

As usual, thanks for your help and suggestions. You have always come through for me on this site.