title: Select average rating from another datatable
body: <p>I have 3 data tables.</p>
  
  <p>In the entries data table I have entries with ID (entryId as primary key).</p>
  
  <p>I have another table called EntryUsersRatings in there are multiple entries that have entryId field and a rating value (from 1 to 5).
   (ratings are stored multiple times for one entryId).
  Columns: ratingId (primary key), entryId, rating (integer value). </p>
  
  <p>In the third data table I have translations of entries in the first table (with entryId, languageId and title - translation).</p>
  
  <p>What I would like to do is select all entries from first data table with their titles (by language ID).
  On a top of that I want average rating of each entry (which can be stored multiple times) that is stored in EntryUsersRatings.</p>
  
  <p>I have tried this:</p>
  
  <pre><code>SELECT entries.entryId, EntryTranslations.title, AVG(EntryUsersRatings.rating) AS AverageRating
  FROM entries 
      LEFT OUTER JOIN
          EntryTranslations ON entries.entryId = EntryTranslations.entryId AND EntryTranslations.languageId = 1
      LEFT OUTER JOIN
          EntryUsersRatings ON entries.entryId = EntryUsersRatings.entryId
        WHERE entries.isDraft=0
      GROUP BY title, entries.entryId
  </code></pre>
  
  <p>isDraft is just something that means that entries are not stored with all information needed (just incomplete data - irrelevant for our case here).</p>
  
  <p>Any help would be greatly appreciated.</p>
  
  <p>EDIT: my solution gives me null values for rating.
  Edit1: this query is working perfectly OK, I was looking into wrong database.
  We also came to another solution, which gives us the same result (I hope someone will find this useful):</p>
  
  <pre><code>    SELECT entries.entryId, COALESCE(x.EntryUsersRatings, 0) as averageRating
      FROM entries  
      LEFT JOIN(
      SELECT rr.entryId, AVG(rating) AS entryRating 
      FROM EntryUsersRatings  rr 
      GROUP BY rr.entryId) x ON x.entryId = entries.entryId 
  </code></pre>