Select fields from another table with multiple foreign keys

Question

I have 4 tables :

Lojas(nomeLoja, *IDloja*, morada, telefone, email)

Bancos(nomeBanco, *IDbanco*, morada)

PlanoContas(*conta*, descricao)

Gestao(NIB, nConta, conta1, conta2, IDloja, IDbanco, *IDgestao*)

conta1 and conta2 are foreign keys to PlanoContas, IDloja foreign key from Lojas and IDbanco foreign key from Bancos.

How can I get a similar output to this:

IDgestao | NIB | nconta | nomeBanco | nomeLoja | descricao_conta1 | descricao_conta2

Answer 1

Just make a join normal, giving names (aliases) for the tables consulted and - on the return of the select - referencing the specific tables and also giving names to them:

select g.IDgestao as IDgestao, g.NIB as NIB, g.nconta as nconta,
       b.nomeBanco as nomeBanco, l.nomeLoja as nomeLoja,
       c1.descricao as descricao_conta1, c2.descricao as descricao_conta2
from Gestao as g
     join Lojas as l on g.IDloja = l.IDloja
     join Bancos as b on g.IDbanco = b.IDbanco
     join PlanoContas as c1 on g.conta1 = c1.conta
     join PlanoContas as c2 on g.conta2 = c2.conta
where ...

Note: the as in the section from is optional; you could simply use Gestao g, Lojas l etc. I’m not sure about the use in select, but it seems to me to be mandatory in this case.