MySQL query to list items that are not of a certain type

Question

I am trying to get a list of companies in our db that are either account_level IN ('basic', 'full'). Our clients are account_level = 'enterprise'. In many cases, an enterprise client will also have rows in the db from then they were basic or full, so I want to exclude any basic/full companies which also have enterprise rows (i.e. I want to exclude our current clients). This way, I can just get a list of companies who are strictly basic or full, and actually aren't our clients yet.

Here's an example of the company table:

1    company a    basic
2    company a    full
3    company b    basic
4    company b    enterprise
5    company c    basic

I want the query to return companies a and c.

I am trying to use:

    SELECT * 
    FROM company c1  
    INNER JOIN company c2 ON c1.id=c2.id
    WHERE c1.company NOT IN  (SELECT c2.company FROM company c2       
    WHERE account_level = 'enterprise')
    AND c1.account_level IN ('full', 'basic')
    ORDER BY c1.company;

but get no results. Can somebody see what I am doing wrong? Sorry, I'm not too experienced yet in mysql. Thanks for your help.

Answer 1

You can get the desired result using a combination of EXISTS and NOT EXISTS:

SELECT DISTINCT c1.company 
FROM company c1
WHERE EXISTS (SELECT 1 
              FROM company AS c2 
              WHERE c1.company = c2.company AND c2.account_level IN ('full', 'basic'))

      AND

      NOT EXISTS (SELECT 1 
                  FROM company AS c3
                  WHERE c1.company = c3.company AND c3.account_level IN ('enterprise')

Or, even simpler:

SELECT DISTINCT c1.company 
FROM company c1
WHERE c1.account_level IN ('full', 'basic'))

      AND

      NOT EXISTS (SELECT 1 
                  FROM company AS c2
                  WHERE c1.company = c2.company AND c2.account_level IN ('enterprise')