How to select from 2 id to bring Different Name

Asked

Viewed 63 times

0

I’m having trouble making a SELECT on Postgres, as follows.

Tabela -> Pessoa

In this table, are stored data of Customers, suppliers, Exporters, Manufacturer, There is no table for each type of "Person".

In another table called Process, I have idexportador and idfabricante, but when I’m gonna do a SELECT, how I will bring the name instead of the ids?

Notice that in the table person has the column nmpessoa, how do I bring the name of both the Exporter and the manufacturer?

Follow my Query:

select
nrprocesso, nmpessoa as Exportador, txdescricaomercadoria
from
processo
inner join
diitem
on
processo.idprocesso = diitem.idprocesso
FULL OUTER JOIN
PESSOA
ON
diitem.idexportador = pessoa.idpessoa
where
idpessoacliente in (select idpessoa from pessoa where nmpessoa ilike '%KULZER%')
/*txdescricaomercadoria ilike '%L5005%'*/
AND
DTABERTURA >= '2019-01-01 00:00:00'
ORDER BY
txdescricaomercadoria
sql postgresql query

  • you need to realize the join twice in the table person referencing the table twice, or whether you will repeat pessoa once for exporter id and once for manufacturer id

  • The Person table will participate in the query with various roles: Customer, Supplier, Exporter and Manufacturer. Something like INNER JOIN pessoa cli ON (diitem.idcliente = cli.idpessoa)
INNER JOIN pessoa exp ON (diitem.idexportador = exp.idpessoa)
INNER JOIN pessoa for ON (diitem.idfornecedor = for.idpessoa)
INNER JOIN pessoa fab ON (diitem.idfabricante = fab.idpessoa)

  • I don’t understand what you want with this FULL OUTER JOIN. It doesn’t seem to make sense to your description of the desired result.

  • Thanks for the explanation, I managed to execute the query by performing the reference Exp. person. I used Full, because I also need results that does not contain Exporter or manufacturer filled.

1 answer

0


Something like that (as @Jmslasher said

select
nrprocesso, exp.nmpessoa as Exportador, 
imp.nmpessoa as importador, txdescricaomercadoria
from
processo
inner join
diitem
on
processo.idprocesso = diitem.idprocesso
FULL OUTER JOIN
PESSOA EXP
ON
diitem.idexportador = EXP.idpessoa
FULL OUTER JOIN
PESSOA IMP
ON
diitem.idimportador = IMP.idpessoa
where
idpessoacliente in (select idpessoa from pessoa where nmpessoa ilike '%KULZER%')
/*txdescricaomercadoria ilike '%L5005%'*/
AND
DTABERTURA >= '2019-01-01 00:00:00'
ORDER BY
txdescricaomercadoria

Browser other questions tagged sql postgresql query

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