CODEWITHSUNDEEP
mysqlsqldatabasesqlite
Jashwant
Jashwant

Reputation: 29005

Nested queries (dependent queries)

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 ?

Upvotes: 4

Views: 415

Answers (3)

richardtz
richardtz

Reputation: 4993

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

Upvotes: 0

SRIRAM
SRIRAM

Reputation: 1888

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

Upvotes: 0

Ahmad
Ahmad

Reputation: 12737

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

Upvotes: 1

Related Questions