Database: exclude 'group by' group if field X in any row has value Y

Question

I'm using MySQL for this.

(I'm stripping out irrelevant joins to keep this a bit clearer)

I'm trying to select companies' addresses, grouping by company_id, but exclude an entire user if they have any addresses in a specific country (with ID 242)

So the first query I tried was:

SELECT c.company_id
FROM companies c
NATURAL JOIN addresses a
WHERE a.country_id != 15
GROUP BY c.company_id

From what I understand, this does the select first, then excludes the rows which have a country_id of 15, then returns the company_id of the remaining rows, which are distinct, as they have been grouped.

So this returns any company who has at least one address outside of country 15. What I need is to exclude any company who has an address in that country.

How can I do this?

Answer 1

SELECT c.company_id
FROM companies c
NATURAL JOIN addresses a
WHERE NOT EXISTS 
   (SELECT * FROM addresses a1 
    WHERE c.company_id = a1.company_id AND a1.country_id = 15)
GROUP BY c.company_id

Alternatively you could use another join instead of exists subquery. You may get better performance using this as the subquery is only resolved once.

SELECT c2.company_id
FROM (SELECT c.company_id
    FROM companies c
    NATURAL JOIN addresses a
    GROUP BY c.company_id) AS c2
LEFT JOIN addresses a2
ON a2.company_id = c2.company_id AND a2.country_id = 15
WHERE a2.company_id IS NULL

Answer 2

SELECT
  c.company_id
FROM
  companies c
  LEFT JOIN addresses a
  on c.company_id = a.company_id and a.country_id=15
WHERE a.country_id is null
GROUP BY c.company_id

Answer 3

Try this:

SELECT c.company_id
FROM companies c
    LEFT JOIN addresses a
        ON c.AddressId = a.Id
WHERE a.country_id != 15
   OR a.country_id IS NULL
GROUP BY c.company_id