Execute UNION and after LEFT JOIN in MySQL

Question

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

Answer 1

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
;