title: INNER JOIN and COUNT in the same query
body: <p>I am having trouble with putting together INNER JOIN and COUNT in the same query.</p>
  
  <p>Tables are:</p>
  
  <pre><code>TABLE STREETS
  
  ID | STREET_NAME
  ------------------------
  1  | Elm street
  2  | Some other street
  3  | Unknown street
  4  | Killer street
  5  | Dead-end street
  
  
  
  
  TABLE ACCIDENTS_STREETS
  
  STREET_ID | ACCIDENT_ID
  -----------------------
     2      | 4
     2      | 7
     2      | 2
     2      | 1
     5      | 3
  </code></pre>
  
  <p>I would like to get the street name where most accidents have occured.</p>
  
  <p>This is for COUNT:</p>
  
  <pre><code>SELECT TOP 1 COUNT(STREET_ID) AS dangerous_street FROM ACCIDENTS_STREETS GROUP BY STREET_ID ORDER BY dangerous_street DESC
  </code></pre>
  
  <p>How to add INNER JOIN there to get only the name of the street?</p>
  
  <p>Any advice is appreciated!</p>