SQL Query help
I have 2 tables that look like this:
Table1: EntityID, FamilyID
Table 2: EntityID, ParentEntityID
Table 1 contains a list of entities with FamilyID column currently all NULLs. Table 2 provides family information between entities. E.g.: an entity can be linked to a parent entity, a grandparent entity, to grandparent, etc.. A single entity can be linked to multiple parent entities too (just like in Family2 in the example below)
I am looking for an SQL that generates and populates a unique family ID for all linked Entities in the Table1. Example, if Table2 has the following data:
0,1
1,2
3,4
4,5
3,6
Than Table1 FamilyID column should be populated as follows:
1, family1
2, family1
3, family2
4, family2
5, family2
6, family2
Note, Table2 currently has 3 million records. And table 1 has about 5.5 million not all entities have parents)...
Thank you, Experts!
Table1: EntityID, FamilyID
Table 2: EntityID, ParentEntityID
Table 1 contains a list of entities with FamilyID column currently all NULLs. Table 2 provides family information between entities. E.g.: an entity can be linked to a parent entity, a grandparent entity, to grandparent, etc.. A single entity can be linked to multiple parent entities too (just like in Family2 in the example below)
I am looking for an SQL that generates and populates a unique family ID for all linked Entities in the Table1. Example, if Table2 has the following data:
0,1
1,2
3,4
4,5
3,6
Than Table1 FamilyID column should be populated as follows:
1, family1
2, family1
3, family2
4, family2
5, family2
6, family2
Note, Table2 currently has 3 million records. And table 1 has about 5.5 million not all entities have parents)...
Thank you, Experts!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!
Please clarify your question... if the MongoDB category is incorrect, then we need to know which dbms actually applies.
If MongoDB is correct, then we need to know if you are looking for a "mongo cli" solution only (pretty tough to write, in my estimation) or would a solution composed of a programming language like Java or a scripting language like Python that manipulates the db be acceptable.
I believe anyone who can offer a workable solution will need to know the answers to these questions.