[SQL] Select rows if all rows match multiple conditions in sql [closed]

My task is almost the same as in this link, but I also need to add a date condition. If flag = 1 and less than today's date, such strings are suitable

CREATE TABLE tablename (
`ID` INTEGER,
`category` VARCHAR(1),
`flag` INTEGER,
`dtt` datetime
);

INSERT INTO tablename
(`ID`, `category`, `flag`, `dtt`)
VALUES
('1', 'A', '1', '2021-01-01'),
('2', 'A', '1', '2022-01-01'),
('3', 'A', '0', '2021-01-01'),
('4', 'B', '1', '2022-01-01'),
('5', 'F', '1', '2026-01-01'),
('6', 'C', '0', '2025-01-01'),
('7', 'C', '1', '2023-01-01');


Expected Result:

1. ('1', 'A', '1', '2021-01-01')
2. ('2', 'A', '1', '2022-01-01')
3. ('4', 'B', '1', '2022-01-01')

I tried to do it this way, but it outputs an extra line (3) with flag 0:

SELECT *
FROM tablename a
WHERE a.category NOT IN (
SELECT b.category
FROM tablename b
WHERE b.category = a.category and (b.flag = 0 or b.dtt > now()) and (b.flag = 1 or b.dtt > now()))


Wrong result:

1. ('1', 'A', '1', '2021-01-01')
2. ('2', 'A', '1', '2022-01-01')
3. ('3', 'A', '0', '2021-01-01')
4. ('4', 'B', '1', '2022-01-01')

Continue reading...