title: Select the lowest price by item in SQL with multiple joins
body: <p>I have multiple tables representing Flights data :</p>
  
  <p>flights :</p>
  
  <pre><code>ID | NMB   | DATE
  1  | CA1   | 2017-05-26
  2  | CA1   | 2017-05-27
  3  | CA2   | 2017-05-26
  4  | CA2   | 2017-05-27
  </code></pre>
  
  <p>airports :</p>
  
  <pre><code>ID | NAME
  1  | JFK
  2  | LAX
  </code></pre>
  
  <p>flights_arrival :</p>
  
  <pre><code>ENTITY_ID (flights) | TARGET_ID (airports)
  1                   | 1
  2                   | 1
  3                   | 2
  4                   | 2
  </code></pre>
  
  <p>flights_price :</p>
  
  <pre><code>ENTITY_ID (flights) | VALUE
  1                   | 455
  2                   | 650
  3                   | 750
  4                   | 880
  </code></pre>
  
  <p>I'm trying to select the lowest price for each flight for each airport of arrival. What I want is this :</p>
  
  <pre><code>ID | ARRIVAL | PRICE
  1  | 1       | 455
  3  | 2       | 750
  </code></pre>
  
  <p>This is the SQL query I constructed but the <code>MIN</code> function applied on the <code>flights_price</code> does not return only the lowest price for the flight but all entries :</p>
  
  <pre><code>        SELECT
              f.id AS id,
              fa.target_id AS arrival,
              MIN(fp.value) AS price
          FROM flights_price AS fp
          JOIN flights AS f ON fp.entity_id=f.id
          JOIN flights_arrival AS fa ON fa.entity_id=f.id
          WHERE
            fa.target_id IN (1, 2)
          GROUP BY
            fa.target_id,
            fp.value,
            f.id;
  </code></pre>
  
  <p>What I am doing wrong ? I also tried subqueries with no success.</p>