MySQL: Get complete orders with a subset of products

Question

MySQL: Get complete orders with a subset of products

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

The problem we have is the following. We have a shop_orders database with orders placed by customers:

id | user_id
1  | 1

On the other hand we have the shop_order_products table, which contains the products that each order contains, where the order_id field relates both tables:

id | order_id | qty | product_id | product_name | product_price |
1  | 1        |  2  | 23         | samsung S5   | 40.5
2  | 1        |  1  | 12         | iPhone 6S    | 80

Just to clarify, in this example order 1 has 2 units of Samsung and 1 unit of iPhone.

We are able to know, through the shop_order_products table, which are the best selling products of our online store, by calling:

 SELECT *, COUNT(*) as sales FROM shop_order_products GROUP BY order_id ORDER BY sales DESC

We need, given a subset of products, to know how many COMPLETE orders we could get. That is, if we had the products that we sell the most are the iPhone 6S and the Samsung Galaxy S5, how many complete orders would we get with those two products. If an order contains both products but also others, it would not be taken into account for our calculation.

mysql mysqli iphone

asked by Jesús Cerezuela Zaplana 11.04.2017 в 08:56

source

2 answers

Generic queries in SQLSERVER [closed] Webview does not load specific audios within the web

score 0 · Answer 1

I do not know if it's exactly what you're looking for, but let's see if I can help you:

SELECT 
    o.*, COUNT(o.id)
FROM
    shop_orders o
    LEFT JOIN shop_order_products op ON op.order_id = o.id
    LEFT JOIN
        (SELECT 
            p.id
        FROM
            product p where p.id = 1 or p.id = 2) 
        AS custom_product ON op.product_id = custom_product.id
GROUP BY o.id
HAVING COUNT(custom_product.id) = 2;

I commented that in the second LEFT JOIN you must add all the products you want to filter (search for 2 products, 1 and 2) and also set the HAVING COUNT with the number of products you have to have the order (2 products in this case)

I hope it helps.

score 0 · Answer 2

From what I understand, you want a query that brings you the number of (for example) iphones 6, but only the orders that have only bought an iPhone 6, that is, if they bought a samnsung as in order 1, Is it not taken into account in the sum?

SELECT COUNT(*),
FROM shop_order_products 
WHERE order_id IN 
  (SELECT order_id 
   FROM shop_order_products 
   GROUP BY order_id 
   HAVING (COUNT = 1)
  )
GROUP BY order_id;