I have the following query nested with subselects
SELECT PEDIDO.ARTICULO , PEDIDO.CANTIDAD_SERVIDA , PEDIDO.LOTE_ARTICULO,
PEDIDO.FECHA_VENCIMIENTO, MIN(BULTO.NUMERO_LINE),
BULTO.CODIGO_CONTENEDOR , SUM(CANTIDAD_EN_CONTENEDOR)
FROM
(SELECT PED_LN.ARTICULO,
SUM(CANTIDAD_SERVIDA) AS CANTIDAD_SERVIDA,
PED_LN.NUMERO_PEDIDO AS NUMERO_PEDIDO,
PED_LN.LOTE_ARTICULO,
PED_LN.FECHA_VENCIMIENTO AS FECHA_VENCIMIENTO
FROM XXOSI_OMPEDLN_SIS_IN_T PED_LN
WHERE
PED_LN.NUMERO_PEDIDO = 'I-99558462321'
and PED_LN.CANTIDAD_SERVIDA > 0
GROUP BY PED_LN.ARTICULO, PED_LN.NUMERO_PEDIDO, PED_LN.LOTE_ARTICULO,
PED_LN.FECHA_VENCIMIENTO) PEDIDO
LEFT JOIN
(SELECT BUL_LN.ARTICULO AS ARTICULO,
BUL_LN.CODIGO_CONTENEDOR AS CODIGO_CONTENEDOR,
SUM(BUL_LN.CANTIDAD) AS CANTIDAD_EN_CONTENEDOR,
BUL_LN.NUMERO_PEDIDO AS NUMERO_PEDIDO,
BUL_LN.LINEA_EN_PEDIDO AS NUMERO_LINE,
BUL_LN.LOTE
FROM XXOSI_OMBULLN_SIS_IN_T BUL_LN
WHERE
BUL_LN.NUMERO_PEDIDO = 'I-99558462321'
GROUP BY BUL_LN.ARTICULO, BUL_LN.CODIGO_CONTENEDOR, BUL_LN.NUMERO_PEDIDO,
BUL_LN.LINEA_EN_PEDIDO, BUL_LN.LOTE) BULTO
ON PEDIDO.ARTICULO = BULTO.ARTICULO
AND PEDIDO.LOTE_articulo = BULTO.LOTE
group by PEDIDO.ARTICULO, PEDIDO.CANTIDAD_SERVIDA, PEDIDO.LOTE_ARTICULO,
PEDIDO.FECHA_VENCIMIENTO, BULTO.CODIGO_CONTENEDOR,BULTO.LOTE
ORDER BY PEDIDO.ARTICULO;
The problem
By doing the following AND ORDERED.LOTE_article = BULTO.LOTE will only bring me products that are the same in the two tables. and the other products leave them in null, as you can see in the following image.
This is because they do not have data in the field LOT__ARTICLE , so What I need is apart from bringing those that are equal in the two tables to show me the information of those who do not have information in the field LOT__ARTICULO , this information if it exists, but as equal or what has a table with the other and some products do not have data in the field LOT__ARTICLE leaves the data as null.
If I take out the AND ORDER.LOTE_article = BULLET.LOTE and other fields of the query so that the query will not be there, I should show something like this:
