title: How can I join a table in MySQL, deriving one boolean result based on some criteria?
body: <p>I have three tables which I am joining in MySQL. For simplicity's sake, let's call them <code>CUSTOMERS</code>, <code>ORDERS</code> and <code>NOTES</code>. My goal is to create one query which is used in a Windows application to generate a list of customers, the <em>number</em> of orders they have, and then a flag to indicate if a human has written some notes.</p>
  
  <p>Before I added the requirement of notes, my query to do this was:</p>
  
  <pre><code>SELECT c.customer `Customer`, COUNT(o.id) `Order Qty`
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.id
  GROUP BY c.customer ASC;
  </code></pre>
  
  <p>Which produces:</p>
  
  <pre><code>Customer      Order Qty
  --------      ---------
  Acme Corp.            2
  Bee Inc.              3
  </code></pre>
  
  <p>I'd like add a boolean column to indicate if any <em>human</em> entered notes exist. (There are automatically generated notes, which are ignored for this query.)</p>
  
  <p>The <code>NOTES</code> table is basically:</p>
  
  <pre><code>id               int(3)
  customer_id      int(3)
  note_datetime    datetime
  is_auto          tinyint(1)
  note             text
  </code></pre>
  
  <p>Any customer may have any number of notes (one to many relationship), and those notes may be automatically generated (<code>is_auto</code> is true) or not.</p>
  
  <p>I'd like to add a column that simply returns true if <em>any</em> of the notes for that customer are <em>not</em> automatic, letting users know that there are human-entered notes to read.</p>
  
  <p>For this example, ACME (1) has three notes, one of which is not automatic. Bee (2) has two automatic notes:</p>
  
  <pre><code>id   customer_id   is_auto
  --   -----------   -------
   1             1         1
   2             1         1
   3             1         0
   4             2         1
   5             2         1
  </code></pre>
  
  <p>I've come up with the following query:</p>
  
  <pre><code>SELECT
      c.customer `Customer`,
      COUNT(o.id) `Order Qty`,
      IF(SUM(IF(n.is_auto, 0, 1) &gt; 0), TRUE, FALSE) `Human Notes`
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.id
  LEFT JOIN notes n ON n.customer_id = c.id
  GROUP BY c.customer ASC;
  </code></pre>
  
  <p>This produces:</p>
  
  <pre><code>Customer    Order Qty    Human Notes
  --------    ---------    -----------
  ACME Corp.          6           TRUE  # (6 orders, should be 2)
  Bee Inc.            6          FALSE  # (6 orders, should be 1)
  </code></pre>
  
  <p>The problem is that rows from the notes table inflates the number of orders artificially (2 orders multiplied by 3 notes; and 3 orders multiplied by 2 notes). I understand that this is because the LEFT JOIN creates duplicate rows of orders which match multiple rows from the <code>NOTES</code> table.</p>
  
  <p>I have not been able to determine a proper way to rewrite this query. What would be a better way to join the notes table so that I get the desired boolean, but avoid extra rows which alter the order count?</p>