title: Join tables, count based on value in column
body: <p>I have two tables:</p>
  
  <p>Table alpha:</p>
  
  <pre><code>ID | Name
  ---------
  1  | A
  2  | B
  3  | C
  </code></pre>
  
  <p>Table beta:</p>
  
  <pre><code>ID | state
  ----------
  1  | 1
  1  | 2
  1  | 2
  2  | 1
  </code></pre>
  
  <p>I would like to get a result like the following:</p>
  
  <pre><code>ID | Count(state=1) | Count (state=2)
  -------------------------------------
  1  | 1              | 2
  2  | 1              | 0
  3  | 0              | 0
  </code></pre>
  
  <p>How do I do this with SQL?
  I can get the table without the result of ID 3, but that is not what I want.</p>
  
  <p>Thanks in advance!</p>