CODEWITHSUNDEEP
mysqlnested
Alberto
Alberto

Reputation: 223

Queries and subqueries in MySQL, show customers with products purchased

i have one problem this is my simple diagram:

relationship diagram

Well I tried a lot and I can not do what I need, I need to display a list of customers who have made purchases, with most purchased products in every purchase made, for example

Customer number 1 ---> BUY:

Customer number 2 --->BUY:

Customer number 3 --->BUY:

The result of my query must be:

query result

Please help me make this mysql query correctly

Upvotes: 0

Views: 424

Answers (1)

Neil
Neil

Reputation: 55382

I can think of two approaches to this. The first is to use a subselect to query the most popular item for each customer:

SELECT nameCustomer AS customer_name, (
    SELECT idItem
      FROM detailSale
      INNER JOIN sales on sales.idDetail = detailSale.idDetail
      WHERE sales.idCustomer = cliente.idCustomer
      GROUP BY idItem
      ORDER BY SUM(amountBuyItem) DESC
      LIMIT 1
  )
  FROM cliente

Note that this query is incomplete as it only shows you the id of the item, so you would need to join the itemForSale table to retrieve its name.

The other approach is to start with a query that will show you all of the items for each customer in order of popularity:

SELECT idCustomer, idItem, SUM(amountBuyItem) AS totalBuyItem
  FROM detailSale
  INNER JOIN sales on sales.idDetail = detailSale.idDetail
  GROUP BY idCustomer, idItem
  ORDER BY idCustomer, idItem DESC

You could then create an aggregate of this query to find the count of the most popular item, and then join back to it to find out which item that was. This has the disadvantage of potentially returning more than one row for each customer.

Upvotes: 1

Related Questions