Let me inform you about the database. it contains data about sales of the supermarket. I think I need just 2 tables, products and sales. needed columns of sales are sales_date, and sales_code. product_name and price should be used from the product table. Product_code is my foreign key. sales_code is basically a sales check. Sales codes are the same for products that were sold together. I mean if you have bought some goods, your sales code is the same for all goods which you bought.

I should find the products 'Bismak 200gr' and 'energizer maximum' which two were bought together, and other goods were bought together with them. I draw the scheme.image is here I don't know how I can implement it. I have written some codes.

select products.product_name as goods, sum(price) as price, sales_code from sales

inner join products on sales.product_code = products.product_code
where product_name like  '%Bismak 200gr waflim%'
or  product_name like '%energizer maximum%'

group by  product_name,products.price, sales_code

result should be like this:

sample data:

sales table

product table

CodePudding user response：

So you want to find the sales codes where one of the products is X and another of the products is Y

SELECT sales_code
FROM 
  sales s
  inner join 
  products p on s.product_code = p.product_code
WHERE 
  p.product_name like '%Bismak 200gr waflim%' or  
  p.product_name like '%energizer maximum%'
GROUP BY sales_code
HAVING COUNT(DISTINCT CASE WHEN p.product_name like '%Bismak 200gr waflim%' THEN 1 ELSE 2 END) = 2

You could then join this back to your sales table to find all the other products sold at the same time, in the same sales codes

The HAVING enforces that both kinds of product be present in a sales code. It would be simpler if your product names were consistent, but the way you've written your query leads me to believe that you might have eg "energiser maximum size 1" and "energiser maximum size 2" etc and want to limp them all together. If your descriptions are consistent, you can ditch the LIKE and do COUNT(DISTINCT product_name)

CodePudding user response：

One approach is to check all sales who have all of the items by aggregation adding per-product flags, then use them as semi-join in your original query:

select 
     products.product_name as goods
    ,sum(price) as price
    ,sales_code 
from 
    sales s
    inner join products p on s.product_code = p.product_code
where s.sales_code in
(
    select sales_code 
    from sales
    group by sales_code
    having 
        2=
        sign(sum(case when p.product_name like '%Bismak 200gr waflim%' then 1 else 0 end))
         
        sign(sum(case when p.product_name like '%energizer maximum%' then 1 else 0 end))
)
group by  p.product_name,p.price,s.sales_code

CodePudding user response：

Here is a query which will find all pairs of products bought together.
We use < in the join to only say "A bought at the same time as B" and not "B bought at the same time as A" which would be a duplicate (and also to avoid saying "A bought at the same time as A"!)

create table products (productName varchar(10), price int);
insert into products values ('A',1),('B',2),('C',3);
CREATE TABLE sales(salesCode int, salesDate date default current_timestamp, product varchar(10));
insert into sales (salesCode, product) values (1,'A'),(1,'B'),(2,'B'),(2,'C');
SELECT
 COUNT(*)"number",
 a.product,
 b.product
FROM sales a
JOIN sales b
ON a.salesCode = b.salesCode
AND a.product < b.product
GROUP BY
 a.product,
 b.product
ORDER BY
 a.product,
 b.product;
GO

number | product | product
-----: | :------ | :------
     1 | A       | B      
     1 | B       | C      

db<>fiddle here