I have this query in sql
SELECT
SI_Articulo = COALESCE(t.SI_Articulo, c.SI_Num_Articulo),
SI_Ubicacion = COALESCE(t.SI_Ubicacion, c.SI_Ubicacion),
SI_OV = COALESCE(c.SI_OV,''),
SI_Ubicacion_Fisica = COALESCE(c.SI_Ubicacion_Fisica,''),
SI_Existencia = COALESCE(t.SI_Existencia, 0),
SI_Cantidad = COALESCE(c.SI_Cantidad, 0),
SI_Cantidad2 = (SELECT COALESCE(c2.SI_Cantidad,0)
FROM SI_Conteo c2
WHERE c2.SI_Num_Conteo = 2 /*AND c2.SI_Num_Articulo = 200002*/
AND t.SI_Ubicacion = c2.SI_Ubicacion),
SI_Dif = COALESCE(c.SI_Cantidad, 0) - COALESCE(t.SI_Existencia, 0),
SI_Dif_Dinero = (COALESCE(c.SI_Cantidad,0) - COALESCE(t.SI_Existencia,0)) * COALESCE(m.SI_Costo_Promedio,0)
FROM SI_Inventario_Teorico_QAD t
LEFT JOIN SI_Conteo c
ON (t.SI_Articulo = c.SI_Num_Articulo
AND c.SI_Num_Conteo = 1
AND t.SI_Ubicacion = c.SI_Ubicacion)
INNER JOIN SI_Maestro_Ref_QAD m
ON (t.SI_Articulo = m.SI_Num_Articulo
OR c.SI_Num_Articulo = m.SI_Num_Articulo)
WHERE c.SI_Num_Articulo = 200002
OR t.SI_Articulo = 200002
Which brings me the next result
My problem is that in the SI_Conteo
table I have the same reference added, but it does not exist in the SI_SI_Inventario_Teorico_QAD
table, so it does not show it to me.
and I can not put FULL JOIN
O FULL OUTER JOIN
Because there is also the same article but already with c.SI_Num_Conteo = 2
then it brings me everything and not only the AND c.SI_Num_Conteo = 1
This is the one that does not bring me