The rewritten title is: Get the products purchased by the first 10 customers

Question

I want to write a SQL query in laravel/php to get the top 10 different products purchased by customers.

My table structure is as follows:

• OrdersTable (Customer ID, Product ID, etc...)
• ProductsTable (product ID, product name, etc...)

This is my query attempt:

SELECT products.*
FROM products
WHERE products.id = [SELECT DISTINCT (products.id)
                     FROM orders
                     WHERE customer.id=id->list(10)]

Answer 1

I can see the direction you're trying to go with your query, but unfortunately that won't give you the top 10 results. Instead, you might get no results at all:

SELECT products.* 
 FROM products 
 WHERE products.id=[SELECT DISTINCT (products.id) 
                 ^^^   FROM orders 
                       WHERE customer.id=id->list(10)]

= means you are looking for an exact match and your subquery should return 10 rows of data. If you use this operation, you will receive this error.

But if you change it to IN, you may get this error

Based on your current attempt, your option is to perform a JOIN. However, I was wondering how you got your top 10? I can see you are looking for a product in the top 10, but based on what? Sales Amount? Order quantity?

In this sense, here is an example of the top 10 products sorted by order quantity.

SELECT P.* 
FROM Products P 
 JOIN ( SELECT product_id
         FROM Orders
        GROUP BY product_id
        ORDER BY SUM(Qty) DESC
    LIMIT 10) O 
ON P.id=O.product_id;

The subquery is not necessary, but I am imitating the subquery you tried, although the process is not exactly the same. Here's the version without the subquery:

SELECT P.*
FROM Orders O 
 JOIN Products P ON O.product_id=P.id
GROUP BY product_id
ORDER BY SUM(Qty) DESC
LIMIT 10;

Or maybe you are looking for the top 10 sorted by sales amount?

SELECT P.*
FROM Orders O 
 JOIN Products P ON O.product_id=P.id
GROUP BY product_id
ORDER BY SUM(UnitPrice*Qty) DESC
LIMIT 10;

Demo fiddle