title: SQL aggregate functions, inner join
body: <p>I am working on writing a sql to get the SID and SNAME. In this task, I need to count which team win the max number of League and find out the SID.</p>
  <ul>
  <li>Leagues(LID, CHAMPION_TID)</li>
  <li>LID: League ID ; CHAMPION_TID: champion team ID</li>
  <li>SUPPORT(SID, LID)</li>
  <li>SPONSORS(SID, SNAME)</li>
  <li>PRIMARY KEY: LID,SID</li>
  </ul>
  <p>Now, I can find out which team win the max number of League through the following SQL:</p>
  <pre><code>SELECT 
      MAX(y.cham) 
  FROM 
      (SELECT 
           CHAMPION_TID, COUNT(L.CHAMPION_TID) AS cham 
       FROM 
           LEAGUES L 
       GROUP BY 
           L.CHAMPION_TID) y, LEAGUES L 
  WHERE
      y.CHAMPION_TID = L.CHAMPION_TID;
  </code></pre>
  <p>I am confusing in the following step. My idea get the LID, then use the join table to display SID and SNAME. But I suck in this step.</p>
  <pre><code>SELECT L.LID, MAX(y.cham) 
  FROM 
      (SELECT CHAMPION_TID, COUNT(L.CHAMPION_TID) AS cham 
       FROM LEAGUES L 
       GROUP BY L.CHAMPION_TID) y, LEAGUES L 
  WHERE 
      y.CHAMPION_TID = L.CHAMPION_TID
  </code></pre>