SQL customer who never order product p1 and p2 together

Question

I do have two table. I want to find customers who never order product p1 and p2 together(there are million rows in this table)

customer_id  2,4,1,4,2,1,3,2,1   

product_id.  p1,p3,p2,p1,p2,p3,p4,p2

Answer 1

If I understand you correctly, with your very limited information, here is the solution. I broke it up to pieces, for you to understand it better

-- to get customers who ordered P1 
Select customer_id from tbl where product_id = 'P1'

-- to get customers who ordered P2
Select customer_id from tbl where product_id = 'P2'

-- to get customers who ordered both P1 & P2
Select customer_id from tbl p1 
inner join tbl p2 on p1.customer_id = p2.customer_id 
where p1.product_id = 'P1' and p2.product_id = 'P2'

-- to get customers who did not ordered both P1 & P2 together
Select * from tbl m
Left Join
(
Select customer_id from tbl p1 
inner join tbl p2 on p1.customer_id = p2.customer_id 
where p1.product_id = 'P1' and p2.product_id = 'P2'
) q on m.customer_id = q.customer_id
Where q.customer_id is null

Answer 2

If you have one table with customer ids and product ids, then you can use aggregation:

select customer_id
from t
group by customer_id
having sum(case when product_id = 'P1' then 1 else 0 end) = 0 or
       sum(case when product_id = 'P2' then 1 else 0 end) = 0;

That is, get customer who have not ordered one of the products.

Note that "ordered together" implies that they are in the same order. However, you data does not provide any information about orders or the timing of purchases.

Answer 3

the answer for this question if i understood it correctly that you want to find customers who have ordered p1 but never p2 and have ordered p2 but never p1.

Select b.customerid, b.productid 
Table1 b 
Left outer join 
(Select customerid, count(distinct productid) 
From table1
Where productid in (‘P1’, ‘P2’)
groupby
Customerid
Having
count(distinct productid) > 1) a 
on (b.customerid = a.customerid)
Where a.customerid is null
And  b.productid in (‘P1’, ‘P2’)