MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


CREATE TABLE table1(
   ID       INTEGER  NOT NULL PRIMARY KEY 
  ,category INTEGER  NOT NULL
);
INSERT INTO table1(ID,category) VALUES (1,1);
INSERT INTO table1(ID,category) VALUES (2,1);
INSERT INTO table1(ID,category) VALUES (3,2);
INSERT INTO table1(ID,category) VALUES (4,2);
INSERT INTO table1(ID,category) VALUES (5,4);
-- 1,4
SELECT *
FROM table1
WHERE (SELECT count(*) FROM table1 WHERE category=1)>0 
  AND (SELECT count(*) FROM table1 WHERE category=4)>0 


ID
category


1
1

2
1

3
2

4
2

5
4


-- 1,3
SELECT *
FROM table1
WHERE (SELECT count(*) FROM table1 WHERE category=1)>0 
  AND (SELECT count(*) FROM table1 WHERE category=3)>0 


ID
category