How to select many values with the same ID in one table in SQL

Question

I've tried for hours, but still not receive the result as expected.

I have Table A that contains the Customer ID and Product Name. I would like to write an SQL to find the customers who have purchased Product A but have not purchased Product C

Table A:

Customer ID	Product Name
001	A
001	B
001	C
002	A
002	B
003	A
003	C
004	A
005	B
006	C

Expected outcome:

Customer ID	Product Name
002	A
002	B
004	A

Answer 1

Another option:

SELECT *
FROM A
WHERE ProductName = 'A' 
      AND CustomerID NOT IN (SELECT CustomerID 
                             FROM A 
                             WHERE ProductName = 'C');

I use a subquery to get Customer IDs who purchased product C and exclude them from the result.

Answer 2

 create table TableA(Customer_ID varchar(10),   Product_Name varchar(10));
 insert into TableA values('001'    ,'A');
 insert into TableA values('001'    ,'B');
 insert into TableA values('001'    ,'C');
 insert into TableA values('002'    ,'A');
 insert into TableA values('002'    ,'B');
 insert into TableA values('003'    ,'A');
 insert into TableA values('003'    ,'C');
 insert into TableA values('004'    ,'A');
 insert into TableA values('005'    ,'B');
 insert into TableA values('006'    ,'C');

Query#1 (using in):

 select * from TableA 
 where customer_id in (select customer_id from TableA where product_name='A')
 and customer_id not in (select customer_id from TableA where product_name='C')

Customer_ID	Product_Name
002	A
002	B
004	A

Query#2 (using exists):

 select * from TableA A
 where exists (select 1 from TableA B where product_name='A' and A.customer_id=B.customer_id) 
 and not exists (select 1 from TableA C where product_name='C' and A.customer_id=C.customer_id) 

Output:

Customer_ID	Product_Name
002	A
002	B
004	A

db<>fiddle here

Answer 3

If you have only one row per product/customer, use not exists:

select customer
from t
where product = 'A' and
      not exists (select 1 from t t2 where t2.customer = t.customer and t2.product = 'C');

This could return duplicates if customer/product rows are duplicated.

An alternative is to use aggregation:

select customer
from t
where product in ('A', 'C')
group by customer
having min(product) = max(product) and min(product) = 'A';

Answer 4

1. Select from table where product equal A or B
2. Left join to table on customer where product equals C
3. Add WHERE left joined product is null

Answer 5

This would do the job

select * from TableA
where Customer_ID not in
    (select distinct Customer_ID
    from TableA
    where Product_Name = 'C')
and Customer_ID in
    (select distinct Customer_ID
    from TableA
    where Product_Name = 'A')