title: SQL query get lowest value from related record, subquery
body: <p>I have the following tables in a mysql database:</p>
  
  <pre><code>mechanics: id, name, distance
  mechanic_zones: mechanic_id, zone, radius
  </code></pre>
  
  <p>The mechanics table records the mechanics, id, name, and their distance from a job (this is actually calculated using the postcode of the mechanic and the postcode of the job but i've simplified this for sake of clarity)</p>
  
  <p>The mechanic_zones table allows the mechanics to define radii for their zones and is used to price a job</p>
  
  <pre><code>mechanics:
  1, Jon, 5.4
  2, Paul, 6.5
  3, George, 20
  
  
  mechanic_zones:
  1, a, 5
  1, b, 10
  1, c, 20
  2, a, 10
  2, b, 20
  2, c, 50
  3, a, 5
  3, b, 10
  3, c, 15
  </code></pre>
  
  <p>Jon has his zones defined as: a - 5 miles, b - 10 miles, and c - 20 miles.</p>
  
  <p>Paul has his zones defined as: a - 10 miles, b - 20 miles and c - 50 miles.</p>
  
  <p>George has his zones defined as: a - 5 miles, b - 10 miles, and c - 15 miles.</p>
  
  <p>I want to be able to find the lowest zone for a mechanic for a job. 
  In the example Jon is 5.4 miles from the job, Paul is 6.5, and George 20.</p>
  
  <p>So the query should return something like:</p>
  
  <pre><code>mechanic_id, name, zone, distance
  2, Paul, A, 6.5
  1, Jon, B, 5.4
  </code></pre>
  
  <p>The job is in Paul's A zones because at 6.5 miles its within his 10 miles defined as zone A.</p>
  
  <p>Its in Jon's B zone because its greater than his 5 miles A zone but less than his 10 miles limit for his B zone.</p>
  
  <p>It's out of zone for George as its greater than his 20 miles C zone.</p>
  
  <p>This is as far as I've got:</p>
  
  <pre><code>SELECT id, name, (distance * 1) as distance_to_job, min(mz.`zone`) as min_zone, min(mz.radius) as min_radius, max(mz.`zone`) as max_zone, max(mz.radius) as max_radius
  FROM mechanics m, mechanic_zones mz 
  WHERE m.id = mz.mechanic_id 
  GROUP BY m.id, postcode 
  HAVING distance_to_job &lt; max_radius 
  ORDER BY distance_to_job ASC, radius ASC
  </code></pre>
  
  <p>Which (i think) gives me all the mechanics that are within zone but doesn't actually figure out which zone the distance is in.</p>
  
  <p>Any help much appreciated</p>