MySQL join same table twice on same column with different value returning most recent row only -

When I do a complex'm otherwise stuck trying to solve a small part.

We have a 'instructions' table and an 'guess' table. There are multiple lines for different types of estimates for the instructions given in 'Estimates'.

Instruction table

  id | Address Position 1 27 Taylor Road, Albion Park NSW 2527 | Ingrid   
 estimate table  
  id | Instruction_id | Base | Basis_date | Base_name1 1 | Contract value. 2012-04-05 | 124000 2 1 | CAMV | 2012-02-01 | 120000 3 1 | Customer estimate | 2012-06-07 | 132000 4 1 Contract value. 2013-01-03 | 140000 5 | 1 | Customer estimate | 2013-02-09 | 145000   
 We are actually included on the 2 instructions in the "instructions" 2 Based on the instructions. Estimate = Estimates for Instructions and estimates: 1) the most recent customer as the customer 'estimate' (Elaijing Adhar_det and Anuman_man as projected estimates and projections estimated) and 2) most recent "Contraktprises' (again, Elaijing Adhar_det and Adhar_guna contact_date and contract_value In).  
 The purpose of the objective will be as follows;    id Address Position | Contract_price | Contract_date | Estimation_data | Estimate 1value 1 | 27 Taylor Road, Albion Park NSW 2527 | Progress | 2013-01-03 | 140000 | 2013-02-09 | 145000   
 I will really appreciate some help there from the SQL Gurus.  
 Many thanks, Trent.   
  
 Try to select  
  i.id, i. address, i.status, p.max_date contract_date, p.basis_value contract_price, e.max_date estimate_date, e.basis_value instructions estimate_value I JOIN LEFT (q1.instruction_id, MAX_DATE, estimates select e basis_value JOIN (where by = 'CustomerEstimate select by' instruction_id group) Q1 instruction_id, Max (basis_date) estimates MAX_DATE e.instruction_id = q1.instruction_id and e.basis_date = q1.max_date) on i.id = e.instruction_id e LEFT JOIN (For q2.instruction_id, MAX_DATE, select the estimates e basis_value JOIN (instruction Select _id, MAX_DATE from MAX (basis_date) where i.id = p.instruction_id q2 at e.instruction_id = q2.instruction_id and e.basis_date = q2.max_date) on 'ContractPrice' group by base = instruction_id) / Code>  
 Output:  
 | ID | Address Position | CONTRACT_PRICE | CONTRACT_DATE | ESTIMATE_VALUE | ESTIMATE_DATE | -------------------------------------------------- -------------------------------------------------- ------------------------ | 1 | 27 Taylor Road, Albion Park NSW 2527 | Progress | 140000 | 2013-01-03 | 145000 | 2013-02-09 |  
 Here is the   demo.