CODEWITHSUNDEEP
mysqlsqljoinmysql5mysql-5.1
Mike Brown
Mike Brown

Reputation: 211

Get all childs by parent id using JOIN

I have a table representing the family tree. It has fields ID, NAME, LEVEL, PARENT_ID.

enter image description here

And the column PARENT_ID is the foreign key and refers to column ID of this table. How can I get a list of all (any level to the bottom of the hierarchy) children knowing only the ID of specific person's row?

sqlfiddle.com

I need to get it in a single query without using a stored procedure calls. Please help make this the most well.

For example, if ID is 7 then result should be:

enter image description here

I use MySQL-5.1.

Edit: If the original issue has not solution, maybe is there solution when maximum level equal to 5 ?

Upvotes: 0

Views: 2308

Answers (1)

Jaaz Cole
Jaaz Cole

Reputation: 3180

EDIT: NOT a Complete solution. The execution trick only follows one of many branches, and fails to deliver more than one grandchild branch. Per Comments. Seen in the results on SQLFiddle, as well.

So, the solution here at SQLFiddle illustrates an interesting execution hack in MySQL that can be taken advantage of.

SELECT 
  @parent_id := id AS id,
  parent_id,
  name,
  level,
  @depth := @depth + 1 AS depth
FROM 
  family_tree t
  join (SELECT @parent_id := 7, @depth := 0) f
WHERE id = @parent_id or parent_id = @parent_id ;

Essentially, declare your @parent_id variable as the root you start with. The join subquery is only executed once. Then, when each row redefines @parent_id, the where clause is reevaluated, and returns a new row, which redefines @parent_id, and the loop continues until there are no more rows.

Upvotes: 2

Related Questions