title: MySQL Select Distinct with Left Join?
body: <p>I am trying to get a list of <code>company_id</code>'s that have no company-level notes. The company may, however, have location-level notes.</p>
  
  <pre><code>company
  -------------------------
  company_id  name  deleted
  1           Foo   0
  2           Bar   0
  3           Baz   0
  
  location
  -----------------------
  location_id  company_id
  6            1
  7            2
  8            3
  
  note
  -----------------------------------------
  note_id  company_id  location_id  deleted
  10       2           6            0         // location-level note
  11       1           7            0         // location-level note
  12       null        8            0         // location-level note
  13       2           null         0         // company-level note
  </code></pre>
  
  <p>I would want my result table to be this:</p>
  
  <pre><code>company_id  name
  1           Foo
  3           Baz
  </code></pre>
  
  <p><strong>Update</strong></p>
  
  <p><code>Foo</code>/<code>company_id = 1</code> does not have a company-level note because the note also has a <code>location_id</code>, which makes it a location-level note. Company-level notes are notes that only link to a company (and not a location).</p>
  
  <p><strong>End of Update</strong></p>
  
  <p>I've tried doing something like this, but it returns an empty set, so I'm not sure if it's working and there aren't any companies without company-level notes or if I'm doing something wrong.</p>
  
  <pre><code>SELECT DISTINCT 
  c.company_id, 
  c.name
  FROM company AS c
  LEFT JOIN note AS n
  ON c.company_id = n.company_id
  WHERE 
  c.deleted = 0 AND
  n.deleted = 0 AND
  n.location_id IS NOT NULL AND 
  n.location_id != 0 AND
  c.company_id = (SELECT MAX(company_id) FROM company)
  </code></pre>
  
  <h1>Revised Accepted Answer by Mike</h1>
  
  <pre><code>SELECT
      company_id,
      name
  FROM company
  WHERE 
      deleted = 0 AND
      company_id NOT IN (
          SELECT DISTINCT 
              c.company_id
          FROM company AS c 
          INNER JOIN note AS n 
          ON c.company_id = n.company_id 
          WHERE (
              n.deleted = 0 AND
                  (n.location_id IS NULL OR 
                  n.location_id = 0)
          )
      );
  </code></pre>