sql - MySQL Select from 3 tables where date varies -

i trying run query select information table conditions on 2 others met based on time , 1 other condition.

the tables are:

lead

lead_id   lead_bname    lead_tname    lead_created   1       abc stores    abc            2015-06-01 12:20:50   2       def hutt      def            2015-07-13 13:52:06 

lead owner

lead_owner_id    lead_id    staff_id    lead_owner_timestamp       1             1         105        2015-06-01 12:20:50       2             1         103        2015-06-01 12:20:51       3             2         105        2015-07-13 13:52:06 

lead status

lead_status_id    lead_id    status_id    lead_status_timestamp       1              1         54          2015-06-01 12:20:50       2              1         56          2015-06-06 10:14:55       3              2         54          2015-07-13 13:52:06 

with query need lead_bname , lead_tname timestamps (not created) dynamic timeframe. (e.g. 2015-06-01 -> 2015-06-03) , matches particular staff_id @ time if earlier or later has changed. same again status.

i attempted below, got 0 result.

select l.lead_id, lead_bname, lead_tname, lead_created  lead l, lead_owner o, lead_status s  lead_owner_timestamp = (       select max(lead_owner_timestamp)       lead_owner       lead_id = l.lead_id)  , ( lead_owner_timestamp <= '2015-06-01' , lead_owner_timestamp >= '2015-06-03')  , staff_id = '103' , lead_status_timestamp = (       select max(lead_status_timestamp)       lead_status lead_id = l.lead_id)  , status_id = '54'  , ( lead_status_timestamp <= '2015-06-01' , lead_status_timestamp >= '2015-06-03')  

expected result

lead_bname    lead_tname    lead_created           status_id abc stores    abc           2015-06-01 12:20:50    54 

this because @ between these 2 dates lead created , status set @ '54' , staff_id assigned '103' latest entry. if staff selected '105' expect receive 0 result, still useful me.

really stuck on one, imagining require inner or outer joins on this, it's little beyond me, hoping here can out.

you're right, need joins , inner selects. try this:

select l.lead_bname, l.lead_tname, l.lead_created, ls.status_id lead l inner join      (select lead_owner_id, lead_id, staff_id, lead_owner_timestamp      lead_owner      lead_owner_timestamp = (select max(lead_owner_timestamp) lead_owner staff_id = 103)         , lead_owner_timestamp >= '2015-06-01'      , lead_owner_timestamp <= '2015-06-03'       , staff_id = 103     ) lo on l.lead_id = lo.lead_id inner join      (select lead_status_id, lead_id, status_id, lead_status_timestamp       lead_status      lead_status_timestamp >= '2015-06-01'       , lead_status_timestamp <= '2015-06-03'      , status_id = 54     ) ls on l.lead_id = ls.lead_id 

notice new revision there two places need change staff id when necessary - line 6 where staff_id = 103 , line 9 and staff_id = 103. created test database data gave in , return row expected when staff id 103 , nothing when 105.