MySQL Query for Parent Child relationship

Question

I want to write a select query to get the complete path from child to parent as:

| Unique Id | Child Code | Parent Code |
|     X     |     9      |     7       |
|     Y     |     7      |     6       |
|     Z     |     6      |     5       |
|     A     |     5      |    NULL     |
|     B     |     11     |     33      |
|     C     |     33     |     22      |
|     D     |     22     |    NULL     |

if code selected is 9 then query should return its all the parents as result which are : 9-7-6-5....

and if code selected is 11 then query should return: 11-33-22....

Thanks.

You have to use recursive stored procedure. Try it it will work for you — Saharsh Shah, Jan 04 '13 at 07:43
Does this article help: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ — Barmar, Jan 04 '13 at 07:43
if you dont want to use stored procedure query multiple times until you get a null — Arun Killu, Jan 04 '13 at 07:47
possible duplicate ::http://stackoverflow.com/questions/11064913/achieve-hierarchy-in-a-less-number-of-mysql-queries — Sashi Kant, Jan 04 '13 at 07:50
[This article might help](http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/) — Muhammad Raheel, Jan 04 '13 at 07:52

score 2 · Accepted Answer · answered Jan 04 '13 at 07:44

Try this query

SELECT *
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_code FROM mytable WHERE unique_id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 9, @l := 0) vars,
        mytable m
    WHERE @r <> 0) T1
JOIN mytable T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC;

score 1 · Answer 2 · edited Apr 09 '14 at 07:34

1

With NULL Parent Value:

SELECT *
FROM <table_name>
ORDER BY COALESCE(<parent_id>,<id>), <id>), category_parent_id IS NOT NULL

With ZERO Parent Value:

SELECT *
FROM <table_name>
ORDER BY COALESCE((SELECT NULLIF(<parent_id>,0)), <id>), NULLIF(<parent_id>,0) IS NOT NULL

edited Apr 09 '14 at 07:34

Carl0s1z

4,683
7
32
47

answered Apr 09 '14 at 07:17

Rohit

11
1

MySQL Query for Parent Child relationship

2 Answers2