SQL. Where condition for multiple values of column-CodePudding

Im looking for some hint when trying to filter for multiple values within column. I'm interested in an "AND" condition for some values in column X (ie. statement Where Column X in (1,2,3) doesn't fulfill my needs).

Consider this example table:

I'm interested in finding COD_OPE that has both status 6 and 7. In this example i'm interested to find only COD_OPE = 3

If i use Where status in (6,7) i'll get cod_ope 1 and 6. Any smart way to find cod_ope = 3? Thank you!

Code for table in the example:

CREATE TABLE [TABLE] (
COD_OPE int,
STATUS int,
Observation_date int
)

INSERT INTO [TABLE] (COD_OPE, STATUS, Observation_date)
VALUES (1,  1,  2022),(1,   1,  2021),  (1, 1,  2020),  (1, 6,  2019),  (1, 6,  2018),  (2, 1,  2022),  (2, 7,  2021),  (2, 4,  2020),  (2, 4,  2019),  (2, 7,  2018),  (3, 1,  2022),  (3, 1,  2021),  (3, 4,  2020),  (3, 7,  2019),  (3, 6,  2018)
select * from [TABLE]

CodePudding user response：

Use aggregation:

SELECT COD_OPE
FROM [TABLE]
WHERE STATUS IN (6, 7)
GROUP BY COD_OPE
HAVING COUNT(DISTINCT STATUS) = 2;