title: MySQL: SELECT and COUNT in same query
body: <p>I have these two tables:</p>
  
  <p><strong>CITY TABLE</strong></p>
  
  <p><img src="https://i.stack.imgur.com/TSnIA.png" alt="enter image description here"></p>
  
  <p><strong>CLUB TABLE</strong></p>
  
  <p><img src="https://i.stack.imgur.com/tv5dX.png" alt="enter image description here"></p>
  
  <p>What I'm trying to do, is to select with the same query all cities that contain published clubs (<code>published</code> field set to 1) and the total of clubs published in that city.</p>
  
  <p>At the moment, I am doing it with two steps, but I would like to improve performance by merging these in just one query.</p>
  
  <pre><code>SELECT c.id, c.name, c.slug 
  FROM city c, club cl 
  WHERE c.id = cl.city_id 
  AND ( SELECT COUNT(*) 
        FROM club cl, city c 
        WHERE cl.city_id = c.id AND cl.published = 1) &gt; 0
  GROUP BY c.id
  </code></pre>
  
  <p>After this, I'm doing a query for each city just to get the COUNT.</p>