GreeKatrina GreeKatrina - 4 months ago 54
SQL Question

MySQL Select Distinct with Left Join?

I am trying to get a list of

company_id
's that have no company-level notes. The company may, however, have location-level notes.

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


I would want my result table to be this:

company_id name
1 Foo
3 Baz


Update

Foo
/
company_id = 1
does not have a company-level note because the note also has a
location_id
, which makes it a location-level note. Company-level notes are notes that only link to a company (and not a location).

End of Update

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.

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)


Revised Accepted Answer by Mike



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)
)
);

Answer

The easiest way to think about this is to first find the all the companies that have company level notes, which you can do with

 select distinct c.company_id
   from company c 
 inner join notes n 
     on c.company_id = n.company_id 
  where n.location_id is null;

Then simply remove these companies from the company select:

select company_id,
       name
  from company
 where company_id not in (select distinct c.company_id
                            from company c 
                          inner join notes n 
                              on c.company_id = n.company_id 
                           where n.location_id is null);

*Updated to use inner join instead of comma-separated join.