MySQL Select Distinct with Left Join?
<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>