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

Question

I use MySQL for this.

(I remove unnecessary connections to make it a little clearer)

I try to select company addresses by grouping company_id, but exclude the entire user if they have addresses in a specific country (with ID 242)

So, the first request 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, it first selects select, then excludes the rows that have country_id from 15, and then returns the company_id of the remaining rows, which are different since they were grouped.

Thus, this returns any company that has at least one address outside of country 15. I need to exclude any company that has an address in this 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 can use another connection instead of the existing subquery. You can get better performance using this, since the subquery is only allowed 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 the following:

 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