title: MySQL Query Join and Count Query
body: <p>I'm trying to pull values from a database for a web app where a moderator can add companies to a list of specified industries. This request needs to pull each industry's name along with a count of attached active companies, as an overview to the moderator.</p>
  
  <p>These are my tables:</p>
  
  <pre><code>companies
  
   ____________________________________
  | id |        company       | active |
  |---------------------------|--------|
  | 12 | Ton-o-Bricks Haulage |    0   |
  | 16 | Roofs 'n' Walls      |    1   |
  | 23 | Handy Services       |    1   |
  | 39 | Carpentharry         |    1   |
  |---------------------------|--------|
  
  industries
   ________________________
  | id |   industry  | mod |
  |------------------|-----|
  |  2 | Roofing     |  2  |
  |  4 | Carpentry   |  2  |
  |  7 | Handyman    |  2  |
  |  8 | Haulage     |  2  |
  |  9 | Electrician |  2  |
  |------------------|-----|
  
  links
   ___________________________
  | id | industry | company  |
  |--------------------------|
  |  1 |     2    |    23    |
  |  2 |     4    |    16    |
  |  3 |     4    |    39    |
  |  4 |     7    |    23    |
  |  5 |     2    |    16    |
  |  6 |     8    |    12    |
  |--------------------------|
  </code></pre>
  
  <p>This query works but does not account for <strong>inactive</strong> companies:</p>
  
  <pre><code>SELECT industries.id, industries.industry, count(links.id) as count FROM industries LEFT JOIN links on links.industry=industries.id WHERE industries.mod=2 GROUP BY industries.id
  
  // -Results =======
  
  2   Roofing     2
  4   Carpentry   2
  7   Handyman    1
  8   Haulage     1
  9   Electrician 0
  </code></pre>
  
  <p>I need it to pull the counts for <strong>active companies only</strong>, but when I try this I get strange results:</p>
  
  <pre><code>SELECT industries.id, industries.industry, count(links.id) as count FROM industries LEFT JOIN links on links.industry=industries.id, companies WHERE industries.mod=2 AND companies.active=1 GROUP BY industries.id
  
  // -Results =======
  
  2   Roofing     6
  4   Carpentry   6
  7   Handyman    3
  8   Haulage     3
  9   Electrician 0
  </code></pre>
  
  <p>I know i'm missing something simple, I just can't figure out what</p>
  
  <p>Thanks,
  Steven</p>