CODEWITHSUNDEEP
mysql
Shyju Abdul Razaq
Shyju Abdul Razaq

Reputation: 41

Mysql get grandparents grandchildren all the way down

+-----------+-----------------+
| AccountID | ParentAccountID |
+-----------+-----------------+
|         1 |            NULL |
|         2 |               1 |
|         3 |               2 |
|         5 |               3 |
|         6 |               5 |
|         7 |               6 |
|         8 |               7 |
|         9 |               8 |
|        10 |               9 |
|        11 |              10 |
|        12 |              11 |
|        13 |              12 |
|        14 |              13 |
|        15 |              14 |
|        16 |              15 |
|        17 |              16 |
|        18 |              17 |
|        19 |              18 |
|        20 |              19 |
+-----------+-----------------+

i am looking for a simple query to list all child and grandchild based on a parent account ID.

For example AccountID (1) s children and grand children are 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 Also AccountID(14)s children and grand children are 15,16,17,18,19,20

i have tried many queries , return only one level up . Please help to solve this. Thanks in advance

Upvotes: 0

Views: 130

Answers (1)

Krishna Rani Sahoo
Krishna Rani Sahoo

Reputation: 1539

You want to the whole hierarchy and not just upto two levels. So for this you have to add another column to your table.

+-----------+-----------------++-----------------+
| AccountID | ParentAccountID | Hierarchy        |
+-----------+-----------------+------------------+
|         1 |            NULL |                  | 
|         2 |               1 |-1->              |
|         3 |               2 |-1->-2->          |
|         5 |               3 |-1->-2->-3->      |
|         6 |               5 |-1->-2->-3->-5->  |
+-----------+-----------------+-+----------------+ etc.

I just giving a hint how to build the structure.

DELIMITER $$

CREATE PROCEDURE build_hierarchy ()
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
 Declare var_AccountId integer;
 Declare var_ParentAccountID integer;
 Declare var_Hierarchy integer;
 Declare cur_all_accounts cursor for select * from accounts;
-- declare NOT FOUND handler
 DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET v_finished = 1;
get_data: LOOP

 FETCH cur_all_accounts INTO var_AccountId, var_ParentAccountID,var_Hierarchy ;

 IF v_finished = 1 THEN 
 LEAVE get_data;
 END IF;

Update Accounts set hierarchy = (select Hierarchy + '-' + accountId + '->' from Accounts 
where accountId = var_ParentaccountId) where AccountID = var_accountId;

 END LOOP get_data;

 CLOSE cur_all_accounts;

END$$

DELIMITER ;

Then run the procedure.

CALL build_hierarchy();

To get the hierachy for parentAccountId = 1, run the following query.

select AccountID from Accounts where Hierarchy like = '%-' + parentAccountId+'->%';

Upvotes: 1

Related Questions