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
Advertisement
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.