0

I have 3 tables

  • cdc_vw_Marbetes_Catalogo_Exis
  • tblMarbetesAdicionales
  • tblMarbetesCongeladas

First I make a UNION of the tables cdc_vw_Marbetes_Catalogo_Exis with tblMarbetesAdicionales.

(SELECT CLAVE_ART, DESCRIPCION_ART, EXISTENCIA_ART, MARCA_ART, exi_num_alm 
        FROM cdc_vw_Marbetes_Catalogo_Exis 
        WHERE EXISTENCIA_ART > 0 AND exi_num_alm = 25)
    UNION
(SELECT clave, descripcion, existencia, marca, almacen 
        FROM tblMarbetesAdicionales 
        WHERE almacen = 25)

Result of the union: Column(CLAVE)

2457-84735-001 (cdc_vw_Marbetes_Catalogo_Exis)
107067860P     (cdc_vw_Marbetes_Catalogo_Exis)
BS-113A        (cdc_vw_Marbetes_Catalogo_Exis)
6200           (tblMarbetesAdicionales)
6300           (tblMarbetesAdicionales)

In table tblMarbetesCongeladas have:

BS-113A
CS-23523
CS-120000
CV206ETCS

But from the result of that union I want to make a LEFT JOIN to get the records that are not in tblMarbetesCongeladas.

I tried like that but it does not work

(SELECT CLAVE_ART, DESCRIPCION_ART, EXISTENCIA_ART, MARCA_ART, exi_num_alm 
        FROM cdc_vw_Marbetes_Catalogo_Exis 
        WHERE EXISTENCIA_ART > 0 AND exi_num_alm = 25)
    UNION
(SELECT clave, descripcion, existencia, marca, almacen 
        FROM tblMarbetesAdicionales 
        WHERE almacen = 25)
    LEFT JOIN tblmarbetescongeladas ON cdc_vw_Marbetes_Catalogo_Exis.CLAVE_ART = tblmarbetescongeladas.clave WHERE cdc_vw_Marbetes_Catalogo_Exis.CLAVE_ART IS NULL

the result that I expect is

2457-84735-001 
107067860P    
6200          
6300  

I appreciate the suggestions

2

You are on the right way but just messed up with the syntax.

SELECT t1.*
  FROM ( SELECT CLAVE_ART
              , DESCRIPCION_ART
              , EXISTENCIA_ART
              , MARCA_ART
              , exi_num_alm 
           FROM cdc_vw_Marbetes_Catalogo_Exis
          WHERE EXISTENCIA_ART > 0 
            AND exi_num_alm = 25

          UNION

         SELECT clave
              , descripcion
              , existencia
              , marca
              , almacen 
           FROM tblMarbetesAdicionales 
          WHERE almacen = 25
       ) AS t1
  LEFT JOIN tblmarbetescongeladas AS t2 ON t1.CLAVE_ART = t2.clave 
 WHERE t2.clave IS NULL
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.