Nested queries (dependent queries)

Question

I need to store a family (i.e. some childs of a parent. Those childs have their own childs and so on..)

So, I created a table family that has following structure

id child child_id parent_id

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

Now,

when I want to fetch all childs of given parent, I can easily do.

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

But,

now I want to fetch the complete family (all descendants of a given parent)

i.e.

I want to fetch all childs which have a given parent_id + all childs that are childs of fetched childs in first query and so on.

Can somebody help me ?

I also, thing there could be better way to store the data initially, so I can fetch it later. Can somebody point out a better way ?

duplicate of [Optimized SQL for tree structures](http://stackoverflow.com/questions/317322/optimized-sql-for-tree-structures) — CL., Nov 08 '12 at 14:26
duplicate of [How to store directory / hierarchy / tree structure in the database?](http://stackoverflow.com/questions/144344/how-to-store-directory-hierarchy-tree-structure-in-the-database) — CL., Nov 08 '12 at 14:27

score 1 · Accepted Answer · answered Nov 08 '12 at 12:41

1

You can write a query that will fetch a child and all of its dependants, but first you would need to re-design your table structure and a enforce a 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

answered Nov 08 '12 at 12:41

Ahmad

12,336
6
48
88

thanks for the article, its really awesome. But it does complicates things by storing id's. I will use that if someone else doesnt comeup with better solution. Btw, can changing db schema or switching to some other schema will help (e.g. initially I was storing a json into a field to store all childs but that failed miserably). Mongodb is not an option here. – Jashwant Nov 08 '12 at 13:25

SRIRAM · Answer 2 · 2012-11-08T12:49:23.533

0

use sub query

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>)

edited Nov 08 '12 at 12:49

answered Nov 08 '12 at 12:11

SRIRAM

1,888
2
17
17

Your query will only retrieve the children of the given parent id, but what about the grand children? – Ahmad Nov 08 '12 at 12:41
Isn't it the same as `select child_id from family where parent_id = ` ? – Jashwant Nov 08 '12 at 12:43

score 0 · Answer 3 · answered Nov 08 '12 at 13:02

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

Regarding the query for traversing all childs, you can use an approach like the one proposed here (http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/), but it wll mean you will need to follow some conventions in your ids and store a 'path' with each node. (which will complicate things if you need to insert something in the middle ot the tree)

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

Hope it helps

Nested queries (dependent queries)

3 Answers3