Return records of one value and not in other values

Apologies for asking such simple question but here I am getting trouble while getting records from single table, my table contains records which have different values in prod_code for similar product_number. I want records of such product_number which have specific prod_code, and not others. For example from below table I need a records which have only prod_code with value CIOFB1 and not CIOFB2, CIOFB2.

product_number	prod_code
A1NU0001093	CIOFB1
A1NU0001093	CIOFB2
A1NU0001093	CIOFB3
A1NU0001094	CIOFB1
A1NU0001094	CIOFB2
A1NU0001094	CIOFB3
A1NU0001095	CIOFB1
A1NU0001096	CIOFB1
A1NU0001096	CIOFB2
A1NU0001096	CIOFB3

I tried creating this query but not getting the records as expected:

SELECT * FROM product_number WHERE prod_code= 'CIOFB1'
AND prod_code NOT IN ('CIOFB2', 'CIOFB3') ;

output:

A1NU0001095   CIOFB1

As other product_number do have all the prod_code

Answer

We could use exists logic here:

SELECT *
FROM yourTable t1
WHERE prod_code = 'CIOFB1' AND
      NOT EXISTS (SELECT 1 FROM yourTable t2
                  WHERE t2.product_number = t1.product_number AND
                        t2.prod_code IN ('CIOFB2', 'CIOFB3'));

The above query says to retain any product’s record having a prod_code of CIOFB1, which product simultaneously does not have prod_code values of CIOFB2 or CIOFB3.