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

Question

Below follows 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;`

but it presents the following error when executing: #1066 - Table/alias contains not only.

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

I thank you in advance for your cooperation.

Answer 1

What you want can be achieved by summing the grouping according to the customer:

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

Answer 2

Table contains appears twice in your query, remove it from 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;