MySql: Get name of a product from another table

Question

I have a table named movimiento which stores the amount of pieces sold by productit

idProducto  Cantidad
   8878       2
   8897       3
   8878       5
   8878       2
   8897       3
   8878       5
   8878       2
   8897       3
   8878       5

and another table called producto in where I store the product detail

idProducto      Nombre
   8878       Tasa Verde
   8897       Tasa Roja

I'm trying to make a query to get the sum of sales by product id, which I have done this :

SELECT idProducto,
   SUM(cantidad) AS c
FROM movimiento
GROUP BY idProducto

and it's working fine, but I'm trying to put in the query result the name of the product based on the name in table producto

The query below is not working :

    select producto.descripcion
from 
(
SELECT movimiento.idProducto,
       SUM(cantidad) AS c
FROM movimiento, producto
GROUP BY movimiento.idProducto
) as rSum
GROUP BY producto.descripcion

an have tried this too, but nothing

select descripcion from producto where idProducto in (
   select idProducto, SUM(cantidad) as c
from movimiento
group by idProducto
   )

Answer 1

Below query is not correct, as you are trying to get producto.descripcion from inline view query. But that column doesn't exist in your inline view.

select producto.descripcion
from 
/* This represents as inline view */
(
SELECT movimiento.idProducto,
       SUM(cantidad) AS c
FROM movimiento, producto
GROUP BY movimiento.idProducto
) as rSum
/* This represents as inline view */
GROUP BY producto.descripcion

I think, what you are looking for is to JOIN the tables and then use GROUP BY on description column, which @Nigel has already shared sample query.

Answer 2

You should be able to use a JOIN to link the id to the description of the product...

SELECT p.descripcion, SUM(m.cantidad) AS c
    FROM movimiento m
    JOIN producto p ON m.idProducto = p.idProducto
    GROUP BY p.descripcion