title: mySQL query syntax - multiple tables and joins
body: <p>I currently have the following tables:</p>
  
  <p><strong>lodges</strong></p>
  
  <pre><code>LodgeID (PK)
  Lodge
  etc
  </code></pre>
  
  <p><strong>scores</strong></p>
  
  <pre><code>ScoreID (PK)
  Score
  CategoryID (FK)
  LodgeID (FK)
  </code></pre>
  
  <p>(also used to calculate ScoreCount and AverageScore)</p>
  
  <p><strong>categoriesFull</strong></p>
  
  <pre><code>CategoryID (PK)
  Category
  </code></pre>
  
  <p><strong>countries</strong></p>
  
  <pre><code>CountryID (PK)
  Country
  </code></pre>
  
  <p><strong>nominations</strong></p>
  
  <pre><code>NominationID (PK)
  LodgeID (FK)
  CategoriesFullID (FK) (--to categoriesFull.CategoryID)
  SW_finalist
  SW_rank
  </code></pre>
  
  <p>So if I have the following:</p>
  
  <p><strong>lodges</strong></p>
  
  <pre><code>LodgeID, Lodge
  798, Borana Lodge
  </code></pre>
  
  <p><strong>scores</strong></p>
  
  <pre><code>ScoreID, LodgeID, CategoryID, Score
  1001, 798, 7, 3
  1002, 798, 7, 6
  1003, 798, 7, 9
  </code></pre>
  
  <p><strong>categoriesFull</strong> </p>
  
  <pre><code>CategoryID 
  7
  </code></pre>
  
  <p><strong>countries</strong></p>
  
  <pre><code>CountryID
  3
  </code></pre>
  
  <p><strong>nominations</strong></p>
  
  <pre><code>NominationID, LodgeID, CategoryID, SW_finalist, SW_rank
  1234, 798, 7, Yes, 2
  </code></pre>
  
  <p>I'd like to be able to output</p>
  
  <pre><code>LodgeID, Lodge, CategoryID, CountryID, ScoreCount, AverageScore, NominationID, SW_finalist, SW_rank
  798, Borana Lodge, 7, 3, 3, 6, 1234, Yes, 2
  </code></pre>
  
  <p>This is what I had before introducing the nominations table:</p>
  
  <pre><code>SELECT 
     ScoreID, COUNT(ScoreID) as scoreCount, AVG(Score) as AverageScore, lodges.LodgeID, 
     Lodge, Country, lodges.CountryID, CategoryID, SW_finalist, SW_rank 
  FROM 
     lodges 
  INNER JOIN 
     countries ON lodges.CountryID = countries.CountryID 
  INNER JOIN 
     nominations ON lodges.LodgeID = nominations.LodgeID 
  INNER JOIN 
     scores ON lodges.LodgeID = scores.LodgeID 
  WHERE 
     lodges.CountryID = 3  
     AND CategoryID = '7' 
  GROUP BY 
     Lodge
  </code></pre>
  
  <p>Which seemed to work to output:</p>
  
  <pre><code>LodgeID, Lodge, CategoryID, CountryID, ScoreCount, AverageScore
  798, Borana Lodge, 7, 3, 3, 6
  </code></pre>
  
  <p>I just need to tag on those three fields from the nominations table. I presume the join on nominations needs to be also be on categoriesFull.CategoryID, but when I try something like </p>
  
  <pre><code>SELECT 
     ScoreID, COUNT(ScoreID) as scoreCount, AVG(Score) as AverageScore, lodges.LodgeID, 
     Lodge, Country, lodges.CountryID, CategoryID, SW_finalist, SW_rank 
  FROM 
     lodges 
  INNER JOIN 
     countries ON lodges.CountryID = countries.CountryID 
  LEFT OUTER JOIN 
     nominations ON lodges.LodgeID = nominations.LodgeID AND nominations.CategoriesFullID = categoriesFull.CategoryID 
  INNER JOIN 
     scores ON lodges.LodgeID = scores.LodgeID 
  WHERE 
     lodges.CountryID = 3 
     AND CategoryID = '7' 
  GROUP BY 
     Lodge
  </code></pre>
  
  <p>I get an error about <code>categoriesFull.CategoryID</code> being an unknown column.</p>
  
  <p>I think all the tables have the IDs they need, but any help with the right joins to get it to work would be much appreciated.</p>
  
  <p>Actually, it doesn't look like it is working - the query I had looks like:</p>
  
  <pre><code>SELECT ScoreID, COUNT(ScoreID) as scoreCount, AVG(Score) as AverageScore, lodges.LodgeID,       lodges.Lodge, Country, lodges.CountryID, categoriesFull.CategoryID, nominations.NominationID,     SW_finalist, SW_rank, 2015_Awards_Participant FROM lodges 
  INNER JOIN countries ON lodges.CountryID = countries.CountryID 
  INNER JOIN scores ON lodges.LodgeID = scores.LodgeID 
  INNER JOIN nominations ON lodges.LodgeID=nominations.LodgeID 
  INNER JOIN categoriesFull ON nominations.CategoriesFullID = categoriesFull.CategoryID AND     scores.categoryID = categoriesFull.CategoryID
  WHERE lodges.CountryID = 3 AND scores.CategoryID=7 GROUP BY Lodge
  </code></pre>
  
  <p>But looks like its doing some multiple counting of the ScoreID - if the Lodge has 3 nominations in the nominations table, the number of scores is being multiplied by 3.</p>