title: SQL join on junction table with many to many relation
body: <p>I have three tables, of which 2 are regular data tables and 1 is a many to many junction table.</p>
  
  <p>The two data tables:</p>
  
  <pre><code>table products
  
  product_id | product_name | product_color
  -----------------------------------------
  1          | Pear         | Green
  2          | Apple        | Red
  3          | Banana       | Yellow
  </code></pre>
  
  <p>and </p>
  
  <pre><code>table shops
  
  shop_id    | shop_location
  --------------------------
  1          | Foo street
  2          | Bar alley
  3          | Fitz lane
  </code></pre>
  
  <p>I have a junction table which contains the <code>shop_id</code>'s and <code>product_id</code>'s:</p>
  
  <pre><code>table shops_products
  
  shop_id    | product_id
  --------------------
  1          | 1
  1          | 2
  2          | 1
  2          | 2
  2          | 3
  3          | 2
  3          | 3
  </code></pre>
  
  <p>I want to select data from products that are in shop with shop_id 3. I tried many examples from here with joins, left joins, inner joins, but I just don't know what I'm doing here and what is going wrong. The query I had, but just returned all products regardless if they are in the specified shop is the following:</p>
  
  <pre><code>SELECT products.product_name, products.product_color
  FROM products
  LEFT OUTER JOIN shops_products
  ON products.product_id = shops_products.product_id
  AND shops_products.shop_id = 3
  LEFT OUTER JOIN shops
  ON shops_products.shop_id = shops.shop_id
  </code></pre>
  
  <p>The expected output is the following:</p>
  
  <pre><code>product_name | product_color
  ----------------------------
  Apple        | Red
  Banana       | Yellow
  </code></pre>
  
  <p>This is in MySQL, thank you for any help, I really appreciate it.</p>