How to create a mysql join query with hierarchical data

Question

I need to create a join query for hierarchical data across 2 tables. These tables can have unlimited amounts of data and their structures are as follows:

group_id      group_name       group_order
   1            group 1            2
   2            group 2            1


field_id    field_name    parent_group    field_order
   1         field 1           1               1
   2         field 2           2               2
   3         field 3           2               1

I am currently able to get the correct format of data using 2 select queries with the second query inside a loop created from the results of the first query on the groups table.

The structure of the data I require from the result is as follows:

-group 2
      - field 3
      - field 2

- group 1
      - field 1

Is it possible to get these results from one mysql query? I have read through the mysql document on hierarchical data by I am confused about how to incorporate the join.

Thanks for looking

score 0 · Answer 1 · answered Mar 10 '11 at 09:39

0

one method

something that may convince you change your db schema

answered Mar 10 '11 at 09:39

Ian Wood

6,515
5
34
73

score 0 · Accepted Answer · answered Mar 10 '11 at 09:42

0

You shouldn't need to think about it in terms of hierarchical data, you should just be able to select your fields and join on your group information. Try something like:

SELECT * 
FROM Fields AS F
INNER JOIN Groups AS G 
ON G.group_id = F.parent_group 
ORDER BY group_order, field_order

Then you will get each fields as a row with the applicable group, also in the correct group order. Your loop should be able to handle the display you need.

answered Mar 10 '11 at 09:42

Jage

7,990
3
32
31

Thank you. Yes you are correct I was using the wrong logic to try and create the statement – Paul Atkins Mar 10 '11 at 10:00

How to create a mysql join query with hierarchical data

2 Answers2