MySQL Query within a Query

[SeanF]

I have a database of customers and products and purchases of products made by customers. Some customers have purchased multiple products.

I need to create a report of all customers who purchased products ordered by the cost of the products they purchased.

One way is to loop through all customers who purchased products and then execute a subsequent search of products they purchased, ordered by cost and LIMIT by 1. The issue is the order of the customer list is not ordered by cost of product.

I am sure there is a way to create a single MySQL query (sub SELECT? UNION?) whih will simply return a single matrix of customers and the most expensive product they purchased ordered by purchase price.

I am drawing a blank... Any help would be appreciated.

[SeanF]

... or is this a SubQuery?... Or a JOIN?

[NickMNS]

I'm no sql expert. (lol... I am a "no-sql" expert though!) This sound like a group by statement.
[w3schools.com...]

[robzilla]

I need to create a report of all customers who purchased products ordered by the cost of the products they purchased.

The total cost of their purchases? Then yes, you could combine GROUP BY with SUM().

For example:

SELECT Customer.name, SUM(Purchases.price) FROM Purchases
LEFT JOIN Customers ON Customers.customer_id = Purchases.customer_id
GROUP BY Purchases.customer_id

[topr8]

or are you looking for a list of:

customers and the most expensive product they purchased

it's not clear what you are trying to do? (by my reading of your post)

subqueries are allowed in MySQL these days, you can perhaps use a join too - or even create a temporary table to select from as part of your query (most likely as part of a stored procedure)

[LifeinAsia]

I need to create a report of all customers who purchased products ordered by the cost of the products they purchased.

Are you looking for what robzilla asked? If so, use:

SELECT Customer.name, SUM(Purchases.price) FROM Purchases
LEFT JOIN Customers ON Customers.customer_id = Purchases.customer_id
GROUP BY Customer.name
ORDER BY SUM(Purchases.price) DESC

to the end.

Or are you asking for the most expensive item by each customer? In that case, use:

SELECT Customer.name, MAX(Purchases.price)
FROM Purchases
LEFT JOIN Customers ON Customers.customer_id = Purchases.customer_id
GROUP BY Customer.name
ORDER BY MAX(Purchases.price) DESC