title: MySQL left join counts
body: <p>I have a left join to a table and want to count columns from it, after grouping by a column of the parent table:</p>
  
  <pre><code>SELECT * , COUNT(list.id) AS listcount, COUNT(uploads.id) AS uploadcount 
  FROM members 
  LEFT JOIN lists ON members.id= list.mid
  LEFT JOIN uploads ON members.id= uploads.mid
  GROUP BY members.id
  </code></pre>
  
  <p>Assume that a user can have either lists or uploads based on the type of user. Then is above query good enough? If not why?</p>
  
  <p>Or do I have to use this query?</p>
  
  <pre><code>SELECT * , l.listcount,   u.uploadcount 
  FROM members 
  LEFT JOIN (select count(lists.id) as listscount,mid from lists group by mid) as l
                                                                  on l.mid = m.id
  LEFT JOIN (select count(uploads.id) as uploadscount
                             ,mid from uploads group by mid) as u on u.mid = m.id
  GROUP BY members.id
  </code></pre>
  
  <p>Or correlated subqueries?</p>
  
  <pre><code>SELECT *,
        (select count(lists.id) as listscount from lists as l where l.mid = m.id
         group by mid) as listcount
        (select count(uploads.id) from uploads as u where u.mid = m.id
         group by mid) as uploadscount 
  FROM members 
  GROUP BY members.id
  </code></pre>
  
  <p>And which is best solution?</p>