mysql - How to retrieve hierarchial parent-child data related by multiple tables? -

i have website contains guitar lessons , exercises, broken down category. have category scales. lesson scales lesson1, contain exercise1_1, exercise1_2. likewise other categories , lessons exercises.

lessons , exercises considered nodes (it drupal site). there node table has node ids, node type (lesson or exercise) , titles.

other info fields these nodes (lesson/exercise text, etc) stored in separate tables each field. instance there drupal_field_data_description table contains description each lesson , exercise.

categories stored in taxonomy term table.

relations among categories handled via taxonomy index table establishes child-parent relation (so have scales, scales->major scales, etc). for question, considering 1 depth of category.

categories of lessons , exercises stored in table drupal_field_data_field_category, maps lessons , exercises category part of.

exercise-lesson child-parent relations stored in table drupal_field_data_field_lesson maps exercises lessons.

here example data:

the categories (drupal_taxonomy_term_data):

tid vid name 1   2   scales 2   2   arpeggios 

the lessons , exercises (drupal_node):

nid type        title 1   lesson      lesson1 2   lesson      lesson2 3   exercise    ex1_1 4   exercise    ex1_2 5   exercise    ex2_1 6   exercise    ex2_2 

the description field lessons , exercises (drupal_field_data_field_description):

entity_type     bundle   entity_id  field_description_value node            lesson   1          lesson1summary node            lesson   2          lesson2summary node            exercise 3          ex1_1summary node            exercise 4          ex1_2summary node            exercise 5          ex2_1summary node            exercise 6          ex2_2summary 

the mapping of lessons , exercises taxonomy (drupal_taxonomy_index):

nid tid 1   1 2   1 3   1 4   1 5   1 6   1 

the mapping of lessons , exercises category (drupal_field_data_field_category) (this 1 seems unnecessary because of taxonomy index):

entity_type bundle   entity_id  field_category_tid node        lesson   1          1 node        lesson   2          1 node        exercise 3          1 node        exercise 4          1 node        lesson   5          1 node        lesson   6          1 

the mapping of exercises lessons (drupal_field_data_field_lesson):

entity_type     bundle      entity_id   field_lesson_target_id node            exercise    3           1 node            exercise    4           1 node            exercise    5           2 node            exercise    6           2 

so... structure, can't figure out how build query return result of form

lesson1 lesson1summary ex1_1 ex1_1summary ex1_2 ex1_2summary lesson2 lesson2summary ex2_1 ex2_1summary ex2_2 ex2_2summary 

note lesson1 , lesson2 in same category.

i need return such data, because category page (that has no subcategories), need display table each lesson shows exercises in lesson.

i in multiple queries, trying better understand sql joins , grouping. also, not dead set on result set shown above. open whatever result set let me readily display data (which via php) in fashion described.

the sql fiddle here

how recommend building such query extract lesson , exercises grouped in logical way (e.g. how show above)?

seems getting lesson , exercises in way amount self join, variety of inner joins on other tables can't piece together...

well, after reading, think figured out:

select n.title, d.field_description_value, n.nid, l.field_lesson_target_id drupal_node n  join drupal_field_data_field_description d on d.entity_id = n.nid  join drupal_taxonomy_index t on t.nid = n.nid  left join drupal_field_data_field_lesson l on l.entity_id = n.nid  order coalesce(l.field_lesson_target_id, n.nid), l.field_lesson_target_id, n.nid 

i based above on this post

my sqlfiddle here.

this new territory me, , while above works, wish understood order , group nuances understand where/how use them.