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