title: Trouble with having a subquery with INNER JOIN
body: <p>I'm having trouble trying to use a subquery with an INNER JOIN. </p>
  
  <p>In the code below, the average rating I'm trying to compute needs to be only the <strong>average rating of that player (a.player)</strong>. </p>
  
  <p>From looking at my script below, <strong>my questions are</strong>:</p>
  
  <p>1) Where should I nest the subquery when using INNER JOIN?</p>
  
  <p>2) There are about 20 names in the table I'm pulling this information from (table 'college'). In the subquery I need 'b.rating' to be for that specific player. Do I need a WHERE clause in the subquery, something like "WHERE player=a.player"?</p>
  
  <pre><code>$query = "SELECT a.player,a.team,a.loc,a.pic,a.rank,b.rating FROM college AS a
          JOIN (SELECT AVG(rating) AS rating FROM college_rating) AS b
          ON a.player=b.player
          ORDER BY rank DESC LIMIT 20";
  $run = mysqli_query($link,$query);
  while($row = mysqli_fetch_array($run)) {
  echo $row['player'] . ' ' . $row['rating'] . ' ' . $row['team'] . '&lt;br&gt;';
  }
  </code></pre>