I have 2 custom modules with the following relationships to cases

Job Header one to many Job Detail
Job Detail one to many Cases

What I would like to do is search cases by the job number which is held in the Job Header table and or have a cases subpanel under job header

using sql it is easy to link them

select c.*
from cases c
left join `cases_cstm` cc on cc.id_c = c.`id`
left join `zz_jobd` jd on jd.id = cc.`zz_jobd_id_c`
left join zz_jobh jh on jh.id = jd.`zz_jobh_id`
where jh.jobno = 3000

Is there anyway I can do searches in sugar like this or will I need to put the job number into cases?