subquery - MySQL where join NOT EXISTS or = 'default' or = '' -

i writing mysql query gets list of leads (leads submission form) , passenger count order lead detail either missing or empty or = default. have joined lead_detail table know how check if exist, not if doesn't. have far -

select l1.id, (     /* sub query passenger count field 22 */      select lm1.`value` lead_detail lm1     lm1.form_id = l1.form_id , lm1.lead_id = l1.id     , lm1.field_number = 22 ) passengers lead l1  /* join our detail table */ left join lead_detail lm2 on (l1.id = lm2.lead_id) lm2.field_number = 29 , lm2.form_id = 1 , lm2.lead_id = l1.id , (lm2.`value` = 'default' or lm2.`value` = '')  /* make sure on lead form 1 , have paid */ , l1.form_id = 1 , l1.payment_status = 'paid' 

just reference, field number 22 = passenger count

field number 29 = form access key (empty, default, or non existent)

it may worth noting gravity forms wordpress plugin custom action trying achieve. lm2 table structure below -

(int) id (int) lead_id (int) form_id (int) field_number (string) value 

hopefully clear trying do. thanks!

you should change alias of lead_detail sub-query, let's lm2, or, alias of same table in main query. example:

/* sub query passenger count field 22 */      select lm2.`value` lead_detail lm2     lm2.form_id = l.form_id , lm2.lead_id = l.id     , lm2.field_number = 22