title: How to sort products by average rating from another table?
body: <p>I'm new to databases, so I haven't wrapped my head around how to use joins, subqueries etc. properly. That's why I'm very unsure how to go about sorting products by the average of another table.</p>
  <p>Here's my usecase:</p>
  <p>I have a straightforward products table in this style</p>
  <pre><code>|id|product|more fields|
  |1 |hat    |feathered  |
  |2 |pants  |jeans      |
  |3 |pants  |cargo      |
  </code></pre>
  <p>And in a second table, produtcs_rating, I have the five-star ratings like</p>
  <pre><code>|id|product_id|rating|
  |1 |    1     |   5  |
  |2 |    2     |   2  |
  |3 |    3     |   4  |
  |4 |    1     |   4  |
  |5 |    3     |   3  |
  </code></pre>
  <p>Now I want to be able to sort these on the website according to the average rating (simple arithmetic mean is fine).
  Would something like this be working?</p>
  <pre><code>SELECT * FROM products ORDER BY (SELECT AVG(rating) AS avg_rating FROM product_ratings) DESC;
  </code></pre>
  <p>Or is this something where I should use a join? And if, how?
  Any help for a beginner would be much appreciated!</p>