refencias cruzadas, tablas cruzadas, cross tab o como querais llamarlo. Ayuda

[mickeystar]

A ver tengo una serie de datos una tabla de pedidos de compra y una tabla de pedidos de ventas.

En una tabla tengo lo siguiguiente:
Ejemp.

Pedido Ven.

PedN Ref. Cant.
----|-----|-- |
1 | 1000| 5 |
2 | 1000| 3 |
3 | 1000| 4 |
4 | 1000| 1 |
----|-----|---|

Pedido Com.


PedN Ref. Und.
--|-----|-|
1| 1000|1|
2| 1000|3|
--|-----|-|

La idea es obtener un resultado como el siguiente:

Resultado:
RefN. Vent. Comp.
-------|--------|---------|
1000 | 13 | 4 |
-------|--------|---------|


Pero no el resultado es otro, esta es la consulta.

SELECT A.REFN,
SUM(CASE when A.UNIDADES>0 THEN (A.UNIDADES) ELSE 0 END) AS 'PEN SER',
SUM(CASE when b.UNIDADES>0 THEN (B.UNIDADES) ELSE 0 END) AS 'PEN LLE'
FROM PEDIVE A
LEFT JOIN PEDICO B ON B.REFN=A.REFN
GROUP BY A.REFN
ORDER BY A.REFN;

Y el resultado obtenido es el siguiente:

RefN. Vent. Comp.
-------|--------|---------|
1000 | 26 | 16 |
-------|--------|---------|

si vamos al detalle sin totalizar el resultado de la consulta es la siguiente:

RefN. Vent. Comp.
-------|--------|---------|
1000 | 5 | 1 |
1000 | 5 | 3 |
1000 | 4 | 1 |
1000 | 4 | 3 |
1000 | 3 | 1 |
1000 | 3 | 3 |
1000 | 1 | 1 |
1000 | 1 | 3 |
-------|--------|---------|
26 16

la consulta de arriba aun que he provado de hacerlas de muchas forma siempre me da el mismo resulta.

He tratado de hacerlo con crystal report y nada.

bueno aver si alguien que entienda un poco mas de SQL me da una mano con este asuntillo.

Gracias de antemano.
Miguel

[Notwen]

Hola mickeystar. La consulta que se me ocurre puede ser algo así:

SELECT * FROM
(SELECT refn, sum(unidades) "PEN SER" FROM PEDIVE GROUP BY refn)
NATURAL JOIN
(SELECT refn, sum(unidades) "PEN LLE" FROM PEDICO GROUP BY refn)

Espero que te sirva.
Saludos.

[mickeystar]

Muchas gracias, me ha sido de gran ayuda.

Aun que logro traer los registros, debo mirar como cruzar los datos de manera que traiga aquellos registros que no tienen conincidencias en ambas tablas.

esta es la consulta

SELECT AX.articulo,AX.definicion,PENSER, PENLLE, ((PENLLE)-(PENSER)) AS DIF from
(SELECT articulo,definicion,sum(unidades-servidas) as 'PENSER'
FROM 2007nq.D_PEDIVE
WHERE FAMILIA='MONTI'
GROUP BY articulo) as AX
NATURAL join
(SELECT articulo,definicion, sum(unidades-servidas) as 'PENLLE'
FROM 2007nq.D_PEDICO GROUP BY articulo) as AZ
ORDER BY AX.ARTICULO;

lo que pasa es que los articulos de la tabla A que no existen en la tabla B y viceversa no aparecen.

si no consigo una solucion tendre que hacer ya un scrip y trabajar con tablas temporales.

[Notwen]

Deberias utilizar un "full outer join" en lugar de un "natural join". Ahora, eso mysql no lo soporta. Tendrias que hacer algo como una union entre un left join y un right join. Podrias probar con algo así:

SELECT * FROM
(SELECT refn, sum(unidades) "PEN SER" FROM PEDIVE GROUP BY refn) r1
LEFT OUTER JOIN
(SELECT refn, sum(unidades) "PEN LLE" FROM PEDICO GROUP BY refn) r2
ON r1.refn = r2.refn
UNION
SELECT * FROM
(SELECT refn, sum(unidades) "PEN SER" FROM PEDIVE GROUP BY refn) r1
RIGHT OUTER JOIN
(SELECT refn, sum(unidades) "PEN LLE" FROM PEDICO GROUP BY refn) r2
ON r1.refn = r2.refn

No se si eso funcionará. Saludos.

[mickeystar]

al final he hecho esto:

SELECT AX.codigo,AX.nombre,
(CASE WHEN PENSER<>0 THEN PENSER ELSE 0 END) AS 'PENS',
(CASE WHEN PENLLE<>0 THEN PENLLE ELSE 0 END) AS 'PENL'
from
(SELECT A.codigo,A.nombre,SUM(b.unidades-b.servidas) as 'PENSER'
FROM 2007nq.articulo A LEFT OUTER JOIN 2007nq.D_PEDIVE B ON A.CODIGO=B.ARTICULO
WHERE A.FAMILIA='SAFTA'
GROUP BY A.CODIGO) as AX
NATURAL join
(SELECT AA.CODIGO,AA.NOMBRE, sum(C.unidades-C.servidas) as 'PENLLE'
FROM 2007NQ.ARTICULO AA LEFT OUTER JOIN 2007nq.D_PEDICO C ON AA.CODIGO=C.ARTICULO
WHERE AA.FAMILIA='SAFTA'
GROUP BY AA.CODIGO) as AZ
WHERE PENSER>0 OR PENLLE>0
ORDER BY CODIGO;

tal cual como la he hecho
utilizando una tercera tabla que es el maestro de articulos.

de esta forma logro sacar lo que quiera.