I need to list the quantity of an 'X' product that each customer bought

Question

I need to list the quantity of an 'X' product that each customer bought

Navigation

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

2

Below is the structure and relationships of my database.

I tried to use the code:

'SELECT 'cliente'.'Nome', 'produto'.'Descricao', 
 'contem'.'QuantItem'
 FROM 'cliente', 'produto', 'contem'
 INNER JOIN 'pedido'
 ON 'cliente'.'Codigo' = 'pedido'.'Codigo'
 INNER JOIN 'contem'
 ON 'contem'.'numero' = 'pedido'.'Numero'
 WHERE 'contem'.'Codigo' = 1;'

However, it displays the following error at run time: # 1066 - Table / alias 'contains' not unique.

I would like to know how I can correct and / or what I have to do so that I can show the desired result.

Thank you in advance for your cooperation.

mysql sql-select

asked by anonymous 12.09.2017 / 20:52

2 answers

0

The table contains appear twice in your query, remove it from

SELECT cliente.Nome
  , produto.Descricao 
  , contem.QuantItem
FROM cliente
INNER JOIN pedido
   ON cliente.Codigo = pedido.Codigo
INNER JOIN contem
   ON contem.Numero = pedido.Numero
INNER JOIN produto
   ON produto.Codigo = contem.Codigo
WHERE contem.Codigo = 1;

12.09.2017 / 21:18

How to change the icon of the mobile menu icon to indicate toggle closing? Degree of class grouping

score 2 · Accepted Answer

What you want can be achieved by adding the grouping according to the client:

SELECT cli.Nome AS cliente,
       prod.Descricao AS produto,
       SUM(co.QuantItem) AS quantidade
  FROM cliente cli
       INNER JOIN pedido p ON p.Codigo = cli.Codigo
       INNER JOIN contem co ON co.Numero = p.Numero
       INNER JOIN produto prod ON prod.Codigo = co.Codigo
 WHERE co.Codigo = 1
 GROUP BY cli.Nome,
          prod.Descricao