0

I have table with below data.

id|child
1|2
1|3
1|4
2|6
2|7
3|9
6|10
6|11
17|18
19|20
18|19

I need to fetch the data like below. i.e super-parent to all child relationship for a id (say here 1 and 19).

Here I have select all child's recursively of super parent id "1"

id|all_child_id
1|2,3,4,6,7,9,10,11

Here I want to select all child's of super parent id '17'.

id|all_child_id
17|18,19,20

I am not aware how to write recursive queries in sql.

Please do not ask me to create intermediate table.

Gaurav Pant
  • 4,029
  • 6
  • 31
  • 54
  • 2
    "I am not aware how to write recursive queries in sql." - Step 1: Find a tutorial or book to learn it. Step 2: Try applying to your problem. Step 3: If you have a specific issue when trying, post it here. – shree.pat18 May 14 '14 at 09:47
  • There are some superb tutorials/articles on recursion in MySQL – Strawberry May 14 '14 at 09:48
  • 1
    the popular one: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ – Barmar May 14 '14 at 09:48
  • @Barmar-- I have already gone through the article you have provided. But i could not find solution for my problem.I need N level recursion. – Gaurav Pant May 14 '14 at 09:50
  • @ shree.pat18- Can you please provide where I can get N level recursion for "MYSQL" – Gaurav Pant May 14 '14 at 09:51
  • 1
    @shree.pat18: Step 0: use a different DBMS than MySQL. MySQL does not support recursive queries –  May 14 '14 at 10:01
  • "*I am not aware how to write recursive queries in sql*" - you can't in MySQL (and you have already been told that when you asked the exact same question 3 hours ago: http://stackoverflow.com/questions/23647606) –  May 14 '14 at 10:03
  • @a_horse_with_no_name You are of course right. I was thinking along the lines of this: http://guilhembichot.blogspot.co.uk/2013/11/with-recursive-and-mysql.html – shree.pat18 May 14 '14 at 10:03
  • @All- I have never posted any question without some research. If any one of you are getting the proper answer from any of the reference material you have shared, then please let me know too.. I hope it will improve me. – Gaurav Pant May 14 '14 at 10:12

2 Answers2

0

What you need here to circumvent recursive queries in MySQL is called "Nested Set Model" (see chapter "The Nested Set Model" here for example)

You can find an implementation example here.

I was looking for better tutorial pages but couldn't find any so far. But I'm sure they exist. So if anyone has better links, pls provide them as a comment here. Thanks!

EDIT: This was the tutorial I was looking for earlier, but sadly its written in german. Maybe the SQL snippets still help you if you decide to and are able to change your data model to fit into a nested set.

Community
  • 1
  • 1
GreenTurtle
  • 1,144
  • 2
  • 21
  • 35
0

It Will work for getting the all the child of your parent 

   declare @parent_id as int;
    set @parent_id =  1;

    WITH RecursiveTable (SR_NO, parent_id,name, Level)
    AS
    (    
        SELECT      MaintTab.SR_NO, 
                    MaintTab.parent_id, 
                    MaintTab.name,
                    0 AS Level
        FROM Product_Master_Final AS MaintTab
        WHERE parent_id = @parent_id

        UNION ALL

        SELECT  MaintTab.SR_NO, 
                    MaintTab.parent_id, 
                    MaintTab.name ,
                    LEVEL + 1
        FROM Product_Master_Final AS MaintTab
            INNER JOIN RecursiveTable Rtab ON
            MaintTab.parent_id = Rtab.SR_NO
    )
    SELECT * FROM RecursiveTable
cracker
  • 4,900
  • 3
  • 23
  • 41