get tree structure data from mysql

Question

i am using mysql, problem is , i have a group id like '101' and i want to get all device id's which is under his account and also those id's which are under its sub groups for eg: if group id is 101: then device_id should be 1. 44444 2. 33333 3. 55555

Table : Groups        --------------------------------------------------------------------------------------
    group_id          |      parent_id          |          group_name
    --------------------------------------------------------------------------------------
    101               |      null               |         matrix
    102               |      101                |         sub_matrix1
    103               |      101                |         sub_matrix2
    104               |      null               |         abc        
    105               |      104                |         sub_abc
    106               |      null               |         mega
    ---------------------------------------------------------------------------------------
    Table : Devices
         --------------------------------------------------------------------------------------
    device_id           |    group_id            |          device_name 
    --------------------------------------------------------------------------------------
    44444               |      101               |         m1
    33333               |      101               |         m1
    22222               |      102               |         m1
    55555               |      103               |         m1        
    88888               |      104               |         m1
    ---------------------------------------------------------------------------------------

Stu · Accepted Answer · 2012-07-31 07:10:59Z

1

Here's a good article about hierarchical data in mysql: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Something like this might work;

SELECT t1.device_name
FROM devices AS t1
INNER JOIN groups AS t2 ON t2.group_id = t1.group_id
WHERE t1.group_id = '101' OR t2.parent_id = '101';

answered Jul 31, 2012 at 7:10

Stu

4,15026 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

get tree structure data from mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related