Union problem in mysql

Question

I am trying to perform a query and is giving the following error:

Unknown column 'BRANCH' in 'field list'

The consultation is as follows::

SELECT TICKET, DATAHORA, TIPO, ESTADO, CRITICIDADE, LOGIN, PARECER, INFOPEND, PROP, DATAPROP, PREV, SRD, PROT, REAB,FILIAL
FROM (
SELECT 
      TC.TN TICKET, TC.CREATE_TIME DATAHORA,  TT.NAME TIPO, TS.NAME ESTADO, TP.NAME CRITICIDADE, US.LOGIN LOGIN,
      CASE  
        WHEN TH.NAME LIKE  '%PARECER%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END PARECER,
      CASE  
        WHEN TH.NAME LIKE  '%INFORMACOESPENDENTES%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END INFOPEND,
      CASE  
        WHEN AR.A_SUBJECT LIKE  '%ATUALIZAÇÃO PROPRIETÁRIO%' THEN LEFT(AR.A_FROM, POSITION('<' IN (AR.A_FROM))-1)
        ELSE ''
        END PROP,
      CASE  
        WHEN AR.A_SUBJECT LIKE  '%ATUALIZAÇÃO PROPRIETÁRIO%' THEN (AR.CREATE_TIME)
        ELSE ''
        END DATAPROP,
      CASE  
        WHEN TH.NAME LIKE  '%PREVISAOATENDIMENTO%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END PREV,
      CASE  
        WHEN TH.NAME LIKE  '%SRD%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END SRD,
      CASE  
        WHEN TH.NAME LIKE  '%PROTOCOLO%' THEN RIGHT(TH.NAME, POSITION('%' IN REVERSE(TH.NAME))-1)
        ELSE ''
        END PROT,
      CASE  
        WHEN TS.NAME LIKE  '%REABERTO' THEN (TS.NAME)
        ELSE ''
        END REAB

   FROM OTRS2.TICKET TC
   INNER JOIN OTRS2.TICKET_TYPE TT ON TC.TYPE_ID = TT.ID
   INNER JOIN OTRS2.TICKET_STATE TS ON TC.TICKET_STATE_ID = TS.ID
   INNER JOIN OTRS2.TICKET_PRIORITY TP ON TC.TICKET_PRIORITY_ID=TP.ID
   INNER JOIN OTRS2.USERS US ON TC.CREATE_BY=US.ID
   INNER JOIN OTRS2.TICKET_HISTORY TH ON TC.ID=TH.TICKET_ID 
   INNER JOIN OTRS2.ARTICLE AR ON TC.ID=AR.TICKET_ID
   UNION 
        SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CUSTOMER.LOGIN FILIAL FROM OTRS2.CUSTOMER_USER CUSTOMER
 ) A

PS: Until I put the UNION, to query was working well and returning the results.

Answer 1

That’s exactly what’s talking about in the message, the selects need to have the same number of columns.

In case you tried to bring only the last column FILIAL in the last select with the table OTRS2.CUSTOMER_USER.

For possible solution, you have to fill in the fields with default values for the other columns that are not in the table OTRS2.CUSTOMER_USER. And even, fill in a default value for the column FILIAL in the other selects.

In the example below, consider the CAMPO_4 as your field FILIAL:

select CAMPO_1, CAMPO_2, CAMPO_3, null as CAMPO_4
  from TABELA_1
 union
select null as CAMPO_1, null as CAMPO_2, null as CAMPO_3, CAMPO_4
  from TABELA_2

Answer 2

It was missing to put null affiliate in the first select and give alias to each of the second select nulls! Thank you to those who helped me!