query for zigzag method in MYSQL

Question

I have mysql table. in that table i have two columns called id_category and id_parent.

id_category  | id_parent 
          1  |  0
          2  |  1
          3  |  1
          4  |  1
          5  |  3
          6  |  2
          7  |  2
          8  |  2
          9  |  5
         10  |  9 
         11  |  9
         12  | 11 
         13  | 11

id_category is auto increment and id_parent have the category values. here i want to get the root id_category for id_category=13 which is equal to id_parent=1. it goes like zigzag l.e id_cat =13 so id_parent=11, so now id_cat =11 so id_parent=9, id_cat =9 so id_parent=5, id_cat =5 so id_parent=3, id_cat =3 so id_parent=1 now condition meet . now i want the category id which have parent id = 1.. in this query expected answer is id_category=3. here for id_category=13 the id_category=3 is the root category this is what i exactly want . now i dont know how to write the query please help me. thanks in advance

Your solution is going to be non-trivial. Your data model is called _adjacency list_. You want to read a few articles on potential solutions: http://stackoverflow.com/questions/10999888/mysql-adjacency-list-model-get-depth and http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ — AgRizzo, Mar 25 '14 at 11:23
But in mike article he didn't say how to get the list in revere order for eg:[10]2 WAY RADIOS ->[6]PORTABLE ELECTRONICS->[1]ELECTRONICS. If i give category id as [10] then i want root category ([1]electronics). this article is somewat useful but not fully :( pls suggent some other article — user3459209, Mar 25 '14 at 11:47

score 1 · Accepted Answer · answered Mar 25 '14 at 11:33

You are representing a list in what is called "adjacency list" form. This is the most common way to represent tree structures in SQL tables. what you are seeking is the path from the root to the node in question, and then the first link in that path. (Or the last link in the path if your query finds them in reverse order).

Unfortunately, finding the entire path in adjacency list form involves a recursive query, which cannot be coded in simple SQL. You have three choices, which may or may not be usable in your case:

Do the search in a programming language, that does repeated queries to the database, recursively. Each step gets you one step closer to the root of the tree. The last step before reaching the root has id_parent equal to the root (1) and id_category equal to what you want. This is the method you have outlined in pseudocode.
Make use of a extension to the SQL language that allows traversal of a tree in a single query. In Oracle, there is a feature that lets you indicate that the two columns you stated connect the entries in a tree structure. However, this feature is Oracle specific. I don't know the equivalent feature, if any, in MYSQL.
Design an alternate table that expresses the tree structure in what is known as "nested set" form. Nested set form is too long to describe here in sufficient detail. You'll have to do some research and read up on it. Writing a query to find the entire path, including the link from the root to the next node, is easy in nested set trees. You may, however, not have the luxury of defining your own data.

Good luck.

query for zigzag method in MYSQL

1 Answers1