Eduard Eduard - 1 year ago 74
SQL Question

Bring a row that is not in one of the 2 tables

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

img1

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.

This is the one that does not bring me

img2

Answer Source

According to your problem that the reference in the table "SI_Conteo" and not in the table "SI_Inventario_Teorico_QAD", you may need to use right join like:

 SI_Conteo right join SI_Inventario_Teorico_QAD 

or use left like:

SI_Inventario_Teorico_QAD left join SI_Conteo 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download