0

I have a table named member with the structure below.

+----+-----------+-------------+  
| id | parent_id | status      |  
+----+-----------+-------------+  
|  1 |         0 | DISTRIBUTOR |  
|  2 |         1 | DISTRIBUTOR |   
|  3 |         2 | DISTRIBUTOR |  
|  4 |         2 | RETAILER    |  
|  5 |         4 | RETAILER    |  
|  6 |         5 | DISTRIBUTOR |  
+----+-----------+-------------+

now I want to find the parent id. For example, if I select id 6 then it will show that the parent id of 6 is 5, but the twist is this: if I selected the id 6 since the status of it's parent 5 is RETAILER it will go up to check what is the parent_id of id 5 but since the parent _id of 5 which is id 4 is RETAILER it will go up again and check the parent_id of id 4 since the parent_id of 4 is 2 and status DISTRIBUTOR the conditon will stop and it will output that the parent_id of id 6 is the id 2

Edit (Query given by user in Comment section)

SELECT id, parent_id, status FROM member WHERE id=6.
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Lara
  • 96
  • 1
  • 10
  • SELECT id, parent_id, status FROM member WHERE id='6'. I didn't have any idea on how to get the result that I want that will find the next parent_id until the condition is true. – Lara Feb 04 '16 at 11:11
  • Possible duplicate of [How to get parent id(root parent) from child id](http://stackoverflow.com/questions/13349877/how-to-get-parent-idroot-parent-from-child-id) – Ali Zia Feb 04 '16 at 11:18
  • yes but the difference is how can I skip the parent_id if RETAILER and continue until the condition meet the DISTRIBUTOR – Lara Feb 04 '16 at 11:22
  • yes like a **chain nodes** looking for the first parent_id status that match to DISTRIBUTOR status. so it will skip the parent_id with the status RETAILER – Lara Feb 04 '16 at 11:34
  • we can not apply/call query recursively..like in programming language. still you can fetch all the record and by creating recursive function(like in PHP or any other language) you can able to get your result – Amit Ramoliya Feb 04 '16 at 11:34
  • 1
    I think you need to use a [stored procedure](http://stackoverflow.com/q/16513418/2032498) to emulate a recursive query, or refactor your database. – Kontrollfreak Feb 04 '16 at 11:44
  • 1
    As Amit says, MySQL has no native support for recursion. Instead, choices include, but are not limited to, the following: - Join the table to itself as many times as could possibly be required - Handle the recursion at the application level, e.g. with a PHP loop - Use a Stored Procedure to handle the recursion - Switch to a Nested Set (or some sort of hybrid model) instead of the 'so-called' Adjacency List model All of these solutions are discussed more thoroughly elsewhere, including a number of excellent, popular, and hence easily googled, articles on Hierarchical data and MySQL. – Strawberry Feb 04 '16 at 11:45
  • thanks! I am doing now with PHP loop, I will post the update of my code. – Lara Feb 04 '16 at 11:48
  • If you want to do it in `mysql` - maybe useful? [Manage hierarchical data with MySQL stored procedures](http://moinne.com/blog/ronald/mysql/manage-hierarchical-data-with-mysql-stored-procedures) – Ryan Vincent Feb 04 '16 at 11:50
  • thanks for the link @RyanVincent – Lara May 22 '17 at 10:11

0 Answers0