Merge different columns in the same result

Asked

Viewed 1,471 times

0

I am trying to generate a SELECT in Postgresql that joins columns of different tables in the same column of the result.
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   

1 answer

1


Two versions to test the 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;

Browser other questions tagged

You are not signed in. Login or sign up in order to post.