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

0

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

    
asked by Eduard 26.07.2017 в 18:08
source

1 answer

1

Instead of using LEFT JOIN for the relationship between SI_SI_Inventario_Teorico_QAD - > SI_Conteo , use FULL OUTER JOIN, so if this relationship does not exist in any of the two tables, it will bring what is specified in your COALESCE :

...
FULL OUTER JOIN SI_Conteo c
...

If you have problems with records on the side of the first table, use RIGHT JOIN.

    
answered by 26.07.2017 / 18:21
source