Duplicados en consulta con INNER JOIN en misma tabla

[razor7_996]

Hola, estoy intentando obtener un resultado que involucra 3 tablas (productos, productosprecios, productosstockmovimientos). Necesito obtener de la tabla Productos, el campo Codigo, de la tabla productosprecios el Precio de Tipo 1 y el Precio de Tipo 2 y aqui lo mas dificil, necesito sumar la Cantidad de Tipo 0 y restarle la Cantidad de Tipo 1 de la tabla productosstockmovimientos

El resultado que busco es este

Código:

Codigo    PrecioGremio    PrecioPublico    Stock
3001704   18.36               0                2

Estos son algunos de los campos de las tablas, para el producto con Codigo 3001704

Tabla productos, para el producto Codigo 3001704

Código:

RecID                   Codigo 	
8,%53y#|!!4¢         3001704

Tabla productosprecios para el IDProducto 8,%53y#|!!4¢

Código:

RecID              	IDProducto 	Precio 	NroLista
8,%53y$z!!4ƒ 	8,%53y#|!!4¢          	18.36 	1
8,%53y$|!!4¨ 	8,%53y#|!!4¢          	0 	2
8,%53y$~!!4l 	8,%53y#|!!4¢          	0 	3
8,%53y$€!!4¸ 	8,%53y#|!!4¢          	0 	4
8,%53y$¢!!4µ 	8,%53y#|!!4¢          	0 	5
8,%53y$£!!4^ 	8,%53y#|!!4¢           	0 	6

Tabla productosstockmovimientos para el IDProducto 8,%53y#|!!4¢

Código:

RecID            IDProducto     IDDeposito         IDFiscal    Cantidad  Tipo
8,(&4fyæ!!#1 	8,%53y#|!!4¢ 	8,&?5ouƒ!!#< 	8+&,-5]=!!#÷ 	1 	0
8,*%,<71!!4m 	8,%53y#|!!4¢ 	8,&?5ouƒ!!#< 	8*-:.ha1!!#^ 	2 	0
8,*%,<`(!!4¿ 	8,%53y#|!!4¢ 	8,&?5ouƒ!!#< 	8+&,-5]=!!#÷ 	2 	1
8,,&11.k!!4i 	8,%53y#|!!4¢ 	8,&?5ouƒ!!#< 	8+&,-5]=!!#÷ 	1 	1
8.(<0=$»!!#< 	8,%53y#|!!4¢ 	8,&?5ouƒ!!#< 	8*-:.ha1!!#^ 	10 	0
8.(<0>6!!!#x 	8,%53y#|!!4¢ 	8,&?5ouƒ!!#< 	8*-:.ha1!!#^ 	10 	1
8.(<0fa¬!!#s 	8,%53y#|!!4¢ 	8,&?5ouƒ!!#< 	8*-:.ha1!!#^ 	5 	0
8.(<0gnc!!#4 	8,%53y#|!!4¢ 	8,&?5ouƒ!!#< 	8*-:.ha1!!#^ 	3 	1

Esta consulta me funciona, me trae exactamente lo que necesito para el IDProducto 8,%53y#|!!4¢ y para todos los productos de la tabla productos pero no me parece para nada elegante. Fijense que para la columna Stock, tengo que hacer un SELECT anidado en el sector de columnas de resultado, al comienzo del SELECT principal.

Me pregunto si alguien sabe si se puede hacer a puro JOIN.

Código MySQL:

SELECT prod.Codigo
     , pp1.Precio AS PrecioGremio
     , pp2.Precio AS PrecioPublico
     , ( SELECT (SUM(pm1.Cantidad) - 
                  (
                    SELECT SUM(pm2.Cantidad)
                    FROM productosstockmovimientos AS pm2
                    WHERE pm2.IDProducto = prod.RecID AND pm2.Tipo=1
                  )
                )
         FROM productosstockmovimientos AS pm1
         WHERE pm1.IDProducto = prod.RecID AND pm1.Tipo=0
       ) AS Stock
FROM productos AS prod
INNER
  JOIN productosprecios AS pp1
    ON prod.RecID = pp1.IDProducto
    AND pp1.NroLista = 1
INNER
  JOIN productosprecios as pp2
    ON prod.RecID = pp2.IDProducto
    AND pp2.NroLista = 2
WHERE prod.Inhabilitado = 0 AND prod.RecID = '8,%53y#|!!4¢'
ORDER BY prod.Codigo ASC
 

Desde ya muchas gracias!

[quimfv]

SELECT codigo, sum(if(tipo=0,cantidad,0))-sum(if(tipo=1,cantidad,0)) as saldo form productosstockmovimientos
group by codigo

...

la funcion if puede ser distinta segun el motor que estes usando (access usa IIF)

[razor7_996]

Hola, muchas gracias por la respuesta, ahora mi consulta esta así

Código MySQL:

SELECT prod.Codigo
     , FORMAT(pp1.Precio,2) AS PrecioGremio
     , FORMAT(pp2.Precio,2) AS PrecioPublico
     , ( SELECT SUM(IF(psm.Tipo=0,psm.Cantidad,0)) - SUM(IF(psm.Tipo=1,psm.Cantidad,0)) 
         FROM productosstockmovimientos AS psm
         WHERE psm.IDProducto =  prod.RecID
       ) AS Stock
FROM productos AS prod
INNER
  JOIN productosprecios AS pp1
    ON prod.RecID = pp1.IDProducto
    AND pp1.NroLista = 1
INNER
  JOIN productosprecios as pp2
    ON prod.RecID = pp2.IDProducto
    AND pp2.NroLista = 2
WHERE prod.Inhabilitado = 0
ORDER BY prod.Codigo ASC
 

El resultado es este

Código:

Codigo        PrecioGremio     PrecioPublico            Stock
04000812             224.00             249.88           NULL
1                     0.75                0.80           NULL
100-3765             32.40                0.00           NULL
1003067              12.00              19.00             -88
1003298              41.28             137.60           NULL
1003304              26.40             88.00                -1
3001704              18.36              0.00                 2

Es posible condicionar la columna "Stock" con esta consulta para que me muestre 0 si es NULL o menor que 0?

Gracias!

PD: Mi servidor es

Servidor: localhost via TCP/IP
Versión del servidor: 5.0.67-community-nt
Versión del protocolo: 10
Usuario: root@localhost
Juegos de caracteres de MySQL: UTF-8 Unicode (utf8)

[quimfv]

En mysql IF(IFNULL(if(tipo=0,cantidad,0),0)<0,0) serviria.

[razor7_996]

Hola, muchísimas gracias por la respuesta, pero tengo un problema, el IF de MySQL espera 3 parámetros, condición, respuesta si verdadero, respuesta si falso http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

Decidí hacer las comparaciones lógicas, después de hacer la operación aritmética, de esa manera escribo menos código.

Hasta ahora mi consulta es esta, fíjense en la parte que dice "QUE PONGO ACA?", intente renombrando el SELECT con "AS opr", pero al ponerlo como parámetro para devolver si verdadero, me dice que no conoce ese campo...

Código MySQL:

SELECT prod.Codigo
     , FORMAT(pp1.Precio,2) AS PrecioGremio
     , FORMAT(pp2.Precio,2) AS PrecioPublico
     , IF( 
          (IFNULL(
                 (SELECT SUM(IF(psm.Tipo=0,psm.Cantidad,0)) - SUM(IF(psm.Tipo=1,psm.Cantidad,0))
                  FROM productosstockmovimientos AS psm
                  WHERE psm.IDProducto =  prod.RecID)
                 ,0))
           <0,QUE PONGO ACA?,0) AS Stock
FROM productos AS prod
INNER
  JOIN productosprecios AS pp1
    ON prod.RecID = pp1.IDProducto
    AND pp1.NroLista = 1
INNER
  JOIN productosprecios AS pp2
    ON prod.RecID = pp2.IDProducto
    AND pp2.NroLista = 2
WHERE prod.Inhabilitado = 0
ORDER BY prod.Codigo ASC
 

Gracias por la ayuda!

[quimfv]

IF(IFNULL(if(tipo=0,cantidad,0),0)<0,IFNULL(if(tip o=0,cantidad,0),0),0)

perdon

[razor7_996]

Hola, intente de dos maneras y una funciono.

Primera Forma, funciona:

Código MySQL:

SELECT prod.Codigo
     , FORMAT(pp1.Precio,2) AS PrecioGremio
     , FORMAT(pp2.Precio,2) AS PrecioPublico
     , IF(IFNULL((SELECT SUM(IF(psm.Tipo=0,psm.Cantidad,0)) - SUM(IF(psm.Tipo=1,psm.Cantidad,0))
                  FROM productosstockmovimientos AS psm
                  WHERE psm.IDProducto =  prod.RecID),0)>0,
          IFNULL((SELECT SUM(IF(psm.Tipo=0,psm.Cantidad,0)) - SUM(IF(psm.Tipo=1,psm.Cantidad,0))
                  FROM productosstockmovimientos AS psm
                  WHERE psm.IDProducto =  prod.RecID),0),
          0) AS Stock
FROM productos AS prod
INNER
  JOIN productosprecios AS pp1
    ON prod.RecID = pp1.IDProducto
    AND pp1.NroLista = 1
INNER
  JOIN productosprecios AS pp2
    ON prod.RecID = pp2.IDProducto
    AND pp2.NroLista = 2
WHERE prod.Inhabilitado = 0
ORDER BY prod.Codigo ASC
 

Esta consulta me devuelve 0 si la sumatoria es NULL o menor que cero, y la sumatoria si la misma es mayor a cero.


Segunda Fomra, no funciona:

Código MySQL:

SELECT prod.Codigo
     , FORMAT(pp1.Precio,2) AS PrecioGremio
     , FORMAT(pp2.Precio,2) AS PrecioPublico
     , (SELECT SUM(IF(IFNULL(IF(psm.Tipo=0,psm.Cantidad,0),0)>0,IF(psm.Tipo=0,psm.Cantidad,0),0)) -        SUM(IF(IFNULL(IF(psm.Tipo=1,psm.Cantidad,0),0)>0,IF(psm.Tipo=1,psm.Cantidad,0),0))
                  FROM productosstockmovimientos AS psm
                  WHERE psm.IDProducto =  prod.RecID
) AS Stock
FROM productos AS prod
INNER
  JOIN productosprecios AS pp1
    ON prod.RecID = pp1.IDProducto
    AND pp1.NroLista = 1
INNER
  JOIN productosprecios AS pp2
    ON prod.RecID = pp2.IDProducto
    AND pp2.NroLista = 2
WHERE prod.Inhabilitado = 0
ORDER BY prod.Codigo ASC
 

Esta consulta me devuelve NULL y Menores que 0, es como si algo se anulara y me devuelve las cosas sin procesar.