Select Union same table

Question

Select Union same table

Navigation

#1 by (1 votes)

0

Could anyone help me with this problem? The following query throws the following error:

# 1054 - Unknown column 'u.idocorrence' in 'where clause'

SELECT DISTINCT * FROM(
        (SELECT  o.*, u.nome AS requerente FROM ocorrencia o, utilizador u WHERE u.idutilizador = 2 AND perfil = 2)
        UNION ALL
    (SELECT  o1.*, u1.nome AS tecnico FROM utilizador u1, ocorrencia o1 WHERE u1.idutilizador != 2 AND u1.perfil = 1) 
    ) AS t WHERE u.idocorrencia = u1.idocorrencia and u.requerente <> u1.tecnico

The tables involved in the query are:

tabela utilizador (idutilizador, nome, perfil);

tabela ocorrencia (idocorrencia, ocorrencia, descricao);

tabela utilizador_ocorrencia (ocorrencia_id, utilizador_id);

mysql where distinct

asked by anonymous 28.02.2016 / 00:34

1 answer

How to do a period-limited search with datepicker? Apply AsyncTask in code that receives double faces

score 1 · Answer 1

You are using a sub-query. From "as t", you can no longer reference the alias "u" and "u1" of the sub-query, that is, from the internal tables. Maybe it would be better if you put the structure of the tables. But see if the query below can help you:

  SELECT distinct o.*, u.nome as requerente, u1.nome as tecnico 
  FROM ocorrencia o
  left join utilizador u  on (o.idocorrencia = u.idocorrencia)
  left join utilizador u1 on (o.idocorrencia = u1.idocorrencia)
  where ((u.idutilizador = 2 AND perfil = 2)
    or (u.idutilizador != 2 AND perfil = 1))
    AND u.nome <> u1.nome

or this:

select o.*, u.nome as requerente, u1.nome as tecnico
from ocorrencia o 
  inner join utilizador_ocorrencia uo on (o.idocorrencia = ocorrencia_id)
  left join utilizar u on (u.idutilizador = uo.utilizador_id and u.perfil = 2)
  left join utilizar u1 on (u1.idutilizador = uo.utilizador_id and u1.perfil = 1)
where u.idutilizador <> u1.idutilizador