Add different columns in the same result

Question

Add different columns in the same result

Navigation

#1 by (1 votes)

0

I'm trying to generate a SELECT in PostgreSQL that pulls columns from different tables into the same column as the result. Home Example:

Usuario
-----------------------
| id | nome  |  regra |
|---------------------|
|   1| João  |  ADM   |
|---------------------|
|   2| Paulo |  USER  |
-----------------------

Permissao
-----------------------------------
| id |     regra     | usuario_id |
|---------------------------------|
|  1 | saida_produto |          2 |
-----------------------------------

SELECT Usuario.id, Permissao.regra, Usuario.regra from Usuario 
LEFT JOIN Permissao 
ON Permissao.usuario_id = Usuario.id 
WHERE Usuario.id = 2;

Resultado
     id  | regra         |   regra    
---------+---------------+-----------
      2  | saida_produto |   USER

Resultado Pretendido
 id  |   regra       
-----+---------------
  2  | saida_produto
  2  | USER

join postgresql select

asked by anonymous 22.07.2016 / 18:13

1 answer

Class active in the menu using include in asp Generate Form by Clicking the Button

score 1 · Accepted Answer

Two versions to test for best performance. First:

with u as (
    select id, regra
    from usuario
    where id = 2
)
select *
from (
    select id, regra
    from u

    union all

    select usuario_id, regra
    from permissao p
    where exists (
        select 1
        from u
        where id = p.usuario_id
    )
) s

Second:

select *
from (
    select id, regra
    from usuario

    union all

    select usuario_id, regra
    from permissao p
    where exists (
        select 1
        from usuario
        where id = p.usuario_id
    )
) s
where id = 2;