mysql - Joining reference tables to a table other than the base table -

basically have query follows:

select main.response_id 'response id',      concat(case          when main.months = 'jan - mar' 'march'         when main.months = 'apr - jun' 'june'         when main.months = 'jul - sep' 'september'         when main.months = 'oct - dec' 'december'         else 'error'     end, ' ', main.year) period,     cnt.country_name country, initm.num_modules 'initial training - number of modules', inittrained.num_instr 'initial training - instructors trained', initpass.pass_num 'initial training - instructors passed', initpass.pass_num / inittrained.num_instr 'initial training - pass percentage' responses_main main -- main responses table left outer join responses_init_training_modules initm using (response_id) -- main init training table left outer join responses_init_training_pass_num initpass using (response_id) -- main init training table left outer join responses_init_training_instr_trained inittrained using (response_id) -- main init training table left outer join country cnt on main.country_id = cnt.country_id -- country table group main.response_id, main.months + ' ' + main.year, cnt.country_name 

what missing query join program table. each of referenced tables (initm, initpass , inittrained) have program_id references program table base table (main) not related program table. current query results in table returning data first program_id + doesn't show program information.

how can derive data set showing main.response_id, prog.program_id, initm.num_modules, initpass.pass_num , inittrained.instr_trained? can't figure out how table referencing both dimensions (main , prog) data 3 referenced tables.

i have drawn out trying achieve here:

any appreciated.

looks data structure causing difficulties.. i'm assuming 3 responses_init_* subclasses of responses_main.

assuming 1 response applies 1 program, approach moving program_id columns responses_main table , go there:

    select ...       program p       join responses_main rm         on rm.program_id = p.program_id   left join responses_init_training_modules ritm         on ritm.response_id = rm.response_id   left join responses_init_training_pass_num ritpn         on ritpn.response_id = rm.response_id   left join responses_init_training_instr_trained ritit         on ritit.response_id = rm.response_id        ... 

notes:

• i'd decide between single , plural table names (responses, program)
• if using quarters save tinyint or store start , end of range tinyint month or date separately. storing kind of range hyphen separated string give issues later on.

update

talk dba, explain need , see if he'll change , makes sense.

in mean time, either run 3 joins program separately:

left join program ritmp        on ritmp.program_id = ritm.program_id left join program ritpnp        on ritpnp.program_id = ritpn.program_id left join program rititp        on rititp.program_id = ritit.program_id 

or use:

(left) join program p on p.program_id = coalesce(ritm.program_id, ritpn.program_id, ritit.program_id) 

but it'll slow , recommend last resort.