Subqueries (dependent queries)

Question

I need to keep the family (i.e. some children of the parent. These children have their children, etc.)

So, I created a family of tables, which has the following structure

ID & baby nbsp; child_id parent_id

When I save a child, I save parent_id along with it.

Now,

when I want to get all the children of a given parent, I can easily do.

select child_id from family where parent_id = <given parent id> 

But,

Now I want to get a complete family (all descendants of this parent)

i.e.

I want to get all children that have a given parent_id + all children that are children of the selected children in the first request, etc.

Can someone help me?

It is also possible that it is better to save the data initially, so I can get it later. Can someone point out a better way?

Answer 1

You can write a query that will retrieve the child and all its dependents, but first you will need to reverse engineer the structure of your table and enforce the protocol when adding new children to make the query work all the time.

Take a look at this very useful article describing and explaining this method.

Answer 2

use helper request

 select GC.grandchildren,children from children C inner join grandchildren GC on C.childid=GC.id and C.childid in (select child_id from family where parent_id = <given parent id>) 

Answer 3

Regarding the design of your table, I think your child_id column child_id redundant, you can just build a hierarchy by setting the parent_id of a specific node, and leave the parent_id blank for the root nodes.

Regarding the request to move all children, you can use an approach similar to the one suggested here (http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/), but that means you will need to follow some agreements in your identifiers and keep a “path” with each node. (which will complicate the situation if you need to insert something in the middle of the tree)

Other database brands have suggestions for this problem (for Oracle, you have a STARTS WITH ... CONNECT BY clause), but none of them are ANSI.

Hope this helps