title: SQL JOIN on 3 tables with multiple one-to-many
body: <p>So, I'm strugglinbg with an SQL query for quit some time now.<br>
  Here is what I'd like to accomplish :</p>
  
  <p>Let's say we have 3 Tables : fruit, farmers &amp; sales  </p>
  
  <p>These Tables are structured and filled as followed.  </p>
  
  <p>Fruits :</p>
  
  <pre><code>__|_fruit______|_taste_
   1| apple      | sweet
   2| banana     | sweet
   3| grapefruit | sour
  </code></pre>
  
  <p>Farmers :</p>
  
  <pre><code>__|_farmer___|_field_____
   1| ben      | apple
   2| mary     | banana
   3| mike     | grapefruit
   4| joanna   | apple
   5| clara    | grapefruit
  </code></pre>
  
  <p>Sales :</p>
  
  <pre><code>__|_fruit______|_amount__
   1| apple      | 50
   2| banana     | 25
   3| apple      | 30
   4| grapefruit | 40
   5| banana     | 45
   6| apple      | 30
  </code></pre>
  
  <p>I'd now like to be able to look which and how many of these fruit have been sold. A result, I could work with, would look something like this<br>
  (WHERE fruits.fruit = "apple") :</p>
  
  <pre><code>fruits.fruit | farmers.farmer | sales.amount
  apple        | ben            | 50
  apple        | joanna         | 30
  apple        | null           | 30
  </code></pre>
  
  <p>or like this<br>
  (WHERE fruits.fruit = "banana") :</p>
  
  <pre><code>fruits.fruit | farmers.farmer | sales.amount
  banana       | mary           | 25
  banana       | null           | 45
  </code></pre>
  
  <p>or like this<br>
  (WHERE fruits.fruit = "grapefruit") :</p>
  
  <pre><code>fruits.fruit | farmers.farmer | sales.amount
  grapefruit   | mike           | 40
  grapefruit   | clara          | null
  </code></pre>
  
  <p>I'd store these results in php and could later determine, which farmer has to be paid what amount of money, because the farmers should be paid appropriate, regarding to the sold amount.  </p>
  
  <p>I'd like to do this with only one SQL query and without a cross reference table.<br>
  My approach to the problem was the following (which did greatly fail) : </p>
  
  <pre><code>SELECT fruits.fruit, farmers.farmer, sales.amount
  FROM fruits
  JOIN farmers ON farmers.field = fruits.fruit
  JOIN sales ON sales.fruit = fruits.fruit
  WHERE fruits.fruit = "apple"
  </code></pre>
  
  <p>The queries last line has to change, in order to get the previously shown results.  </p>
  
  <p>Is it reasonable to do this without a crossreference table?<br>
  Is it possible to do it?<br>
  Is there a completely different approach which would to be preferred here?</p>
  
  <p>Thanks in advance  </p>
  
  <p>EDIT#1 : added a missing entry in sales (6)<br>
  EDIT#2 : MySQL - Version is 5.7.17</p>