How many customers bought a product A in the same order as product B-CodePudding

I have a table daily_orders where I wanted to find the Customers who bought mobile covers in the same order as mobile phone. Here is the table structure

CREATE TABLE daily_orders (
  Order_date datetime,
  orderid TEXT NOT NULL,
  CustomerID int NOT NULL,
  Product text ,
  Product_Category text,
  Units int
);
-- insert some values
INSERT INTO daily_orders VALUES ('2021-06-15 20:06:31','AABS123',46162,'sku1223','mobile phone',1);
INSERT INTO daily_orders VALUES ('2021-07-01 13:21:22','AABWA23',87949,'sku213','laptops',1);
INSERT INTO daily_orders VALUES ('2021-06-11 07:01:25','AA12WA13',122123,'sku1223','mobile phone',1);
INSERT INTO daily_orders VALUES ('2021-06-11 07:01:25','AA12WA13',122123,'sku1223','mobile covers',2);

OUTPUT should be

CustomerID 
122123

The query that I tried

select a.customerID from daily_orders a inner join daily_orders b
on a.CustomerID = b.CustomerID
where a.OrderID = b.OrderID and a.CustomerID = b.CustomerID

But I'm not getting the desired o/p. Any suggestion would be helpful.

CodePudding user response：

You can join the table with itself (one instance for covers, the other one for phones) to get the result you want. For example:

select c.CustomerId
from daily_orders c
join daily_orders p on p.orderid = c.orderid
where c.Product_Category = 'mobile covers'
  and p.Product_Category = 'mobile phone'

CodePudding user response：

This is a "does something exist" query, best fulfilled using exists.

Also note that Text data types have long been deprecated and should no longer be used, always specify varchar(n).

select o.CustomerID 
from daily_orders o
where Product_Category = 'mobile phone'
and exists (
  select * from daily_orders o2 
    where o2.orderid = o.orderid 
      and o2.Product_Category = 'mobile covers'
);

CodePudding user response：

another option is to use string_agg

select t.customerid
from   ( select d.customerid,
                string_agg(convert(varchar, d.Product_Category), ',') cat
         from   daily_orders d
         group by d.customerid
       ) t  
where  t.cat = 'mobile phone,mobile covers'

DBFiddle here

CodePudding user response：

Assuming that the data type of orderid and Product_Category is VARCHAR and not TEXT and there is no case of different CustomerIDs for each orderid, first filter the table to get only customers who bought 'mobile covers' and/or 'mobile phone' and then use aggregation to get only the customers who bought both in the same order:

SELECT DISTINCT CustomerID 
FROM daily_orders 
WHERE Product_Category IN ('mobile covers', 'mobile phone')
GROUP BY CustomerID, orderid 
HAVING COUNT(DISTINCT Product_Category) = 2;

See the demo.

CodePudding user response：

The below query should work if you change the datatype from text to varchar

Table Definition

CREATE TABLE daily_orders (
  Order_date datetime,
  orderid varchar(20) NOT NULL,
  CustomerID int NOT NULL,
  Product varchar(20),
  Product_Category varchar(20),
  Units int
);

Solution

SELECT dor1.customerid,
       dor1.product_category,
       dor2.product_category
FROM   daily_orders dor1
       inner join daily_orders dor2
               ON dor1.customerid = dor2.customerid
                  AND dor1.orderid = dor2.orderid
WHERE  dor1.product_category <> dor2.product_category
       AND dor1.product_category = 'mobile phone'
       AND dor2.product_category = 'mobile covers';