Mysql get where not in with a twist

Question

I want to get the firms that dont have contact method main_phone or phone This is my schema

Firms table
+----+-------+
| id | name  |
+----+-------+
|  1 | Firm1 |
|  2 | Firm2 |
|  3 | Firm3 |
+----+-------+

Contacts
+----+----------+---------+
| id |  label   | firm_id |
+----+----------+---------+
|  1 | Contact1 |       1 |
|  2 | Contact2 |       1 |
|  3 | Contact3 |       2 |
|  4 | Contact4 |       3 |
+----+----------+---------+

contact_methods
+----+-------------+------------+
| id | method_type | contact_id |
+----+-------------+------------+
|  1 | main_phone  |          1 |
|  2 | main_fax    |          1 |
|  3 | email       |          1 |
|  4 | main_fax    |          4 |
|  5 | main_fax    |          3 |
|  6 | phone       |          2 |
|  7 | main_mobile |          1 |
|  8 | url         |          4 |
+----+-------------+------------+

and this is my query

SELECT 
firms.id
FROM firms 
JOIN contacts ON (contactable_id = firms.id)
JOIN contact_methods ON contacts.id = contact_methods.contact_id
WHERE 
firms.active = 1
AND
contact_methods.method_type NOT IN ('mobile','phone','main_mobile','main_phone')

I am getting all firms :s

Answer 1

Your code checks whether each firm has any contact type that does not belong to the list - while you want to ensure that none of the firm contact does.

One option uses aggregation:

select f.*
from firm f
left join contacts c on c.firm_id = f.id
left join contact_methods cm on cm.contact_id = c.id
group by f.id
having not max(cm.method_type in ('mobile','phone','main_mobile','main_phone')) <=> 1

Alternatively, you can use not exists:

select f.*
from firm f
where not exists (
    select 1
    from contacts c
    inner join contact_methods cm on cm.contact_id = c.id
    where c.firm_id = f.id and cm.method_type in ('mobile','phone','main_mobile','main_phone')
)