Choosing a NOT IN and Many to Many relationship
I have many, many relationships
--------------------
| 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 build a query so that if I'm a user of type 3, don't return that user at all. So far, I have a query that excludes users who are of type 3, but they return if other types are assigned to them as well.
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
since this user is assigned to multiple types, how can I exclude users of type 3 no matter how many other types are they assigned?
source to share
Try this for your suggestion where
:
where t.user_id not in (select user_id from usertypes where type_id = 3)
In MySQL, the following might 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 if you are using another table called types
. "u" would be the best alias.
source to share
The subquery IN()
should return user_id
which one has 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)
This can also be done by using LEFT JOIN
for the same subquery looking NULL
in the table type_type
.
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
In fact, the combined subquery is not even needed. It will work with simple 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
source to share