Selecting with NOT IN and Many to Many relationship

Question

I have a many to many relationship

--------------------
| user_id | name   |
--------------------
| 1       | user1  |
--------------------
| 2       | user2  |
--------------------
| 3       | user3  |
--------------------


--------------------
| type_id | name   |
--------------------
| 1       | type1  |
--------------------
| 2       | type2  |
--------------------
| 3       | type3  |
--------------------

---------------------
| user_id | type_id |
---------------------
| 1       | 1       |
---------------------
| 1       | 2       |
---------------------
| 1       | 3       |
---------------------
| 2       | 1       |
---------------------
| 2       | 2       |
---------------------
| 3       | 3       |
---------------------

I'm trying to construct a query so that if I user is of type 3, do not return that user at all. So far I have a query that excludes users who are of type 3, but they are being returned if other types are also assigned to them.

SELECT COUNT(DISTINCT `t`.`user_id`) 
FROM `user` `t` 
LEFT OUTER JOIN `type` `type_type` ON (`t`.`user_id`=`type_type`.`user_id`) 
LEFT OUTER JOIN `type` `type` ON (`type`.`type_id`=`type_type`.`type_id`) 
WHERE ((type.type_ids NOT IN (3));

the above query will still return user_id 1 because that user is assigned to multiple types, how do I eliminate users of type 3, no matter how many other types they are assigned to?

Answer 1

Try this for your where clause:

where t.user_id not in (select user_id from usertypes where type_id = 3)

In MySQL, the following may be more efficient:

where not exists (select 1 from usertypes ut where ut.type_id = 3 and ut.user_id = t.user_id)

By the way "t" is a lousy alias for users, especially when you are using another table called types. "u" would be a better alias.

Answer 2

Your IN() subquery needs to return the user_ids which have a type_id = 3.

SELECT
  COUNT(DISTINCT `t`.`user_id`)
FROM 
 `user` `t` 
WHERE `user_id NOT IN (SELECT `user_id` FROM `type_type` WHERE `type_id` = 3)

It can also be done using a LEFT JOIN against the same subquery, looking for NULLs in the type_type table.

SELECT
  COUNT(DISTINCT `t`.`user_id`)
FROM 
 `user` `t` 
 LEFT JOIN (
   SELECT `user_id` FROM `type_type` WHERE `type_id` = 3
 ) user3 ON t.user_id = user3.user_id
WHERE user3.type_id IS NULL

http://sqlfiddle.com/#!2/b36b1/7

Actually the joined subquery isn't even necessary. It will work with a plain LEFT JOIN.

SELECT
  COUNT(DISTINCT `t`.`user_id`)
FROM 
 `user` `t` 
 LEFT JOIN type_type ON t.user_id = type_type.user_id AND type_type.type_id = 3
WHERE type_type.type_id IS NULL

http://sqlfiddle.com/#!2/b36b1/10