How to select records from a table by column that have some values ​​and do not have others?

Question

MySQL

Hello everybody!
There is a table filled with test data:
insert into ticket (user_id, type) values ​​(1, 1);
insert into ticket (user_id, type) values ​​(1, 2);
insert into ticket (user_id, type) values ​​(1, 3);
insert into ticket (user_id, type) values ​​(1, 4);
insert into ticket (user_id, type) values ​​(2, 1);
insert into ticket (user_id, type) values ​​(2, 2);
insert into ticket (user_id, type) values ​​(2, 3);
insert into ticket (user_id, type) values ​​(3, 1);
insert into ticket (user_id, type) values ​​(3, 2);
insert into ticket (user_id, type) values ​​(4, 4);
insert into ticket (user_id, type) values ​​(5, 2);
insert into ticket (user_id, type) values ​​(5, 3);
insert into ticket (user_id, type) values ​​(6, 3);
insert into ticket (user_id, type) values ​​(7, 1);
How to select all user_id's that have, for example, type 2 and 3 and not 1 and 4? That is, the query should return user_id = 5.
Or select all user_ids that, for example, have type 1 and 2 and not 3 and 4? That is, the query should return user_id = 3.
The result should be grouped by user_id.

Answer 1

SELECT DISTINCT `user_id`
  FROM `ticket`
  WHERE `user_id` IN (SELECT `user_id` FROM `ticket` WHERE `type` = 2)
    AND `user_id` IN (SELECT `user_id` FROM `ticket` WHERE `type` = 3)
    AND `user_id` NOT IN (SELECT `user_id` FROM `ticket` WHERE `type` = 1)
    AND `user_id` NOT IN (SELECT `user_id` FROM `ticket` WHERE `type` = 4)